base Questions - RunSubmit.com

Finding out the name and path of the program being run

2009-11-06T11:10:55Z

How can I determine the name and path of the program I am currently running? (In Windows, sas 9.1.3 - but techniques for other versions / environments would also be good to know..)

Finding the # of days between two dates?

2009-10-30T11:11:36Z

I have data like:

 patid             startdate                    endday 
01S1003            9/30/2008                 3/16/2009

How do I find the number of days bewteen startdate and endday?

How do I programmatically create New Catalogs?

2010-01-15T14:22:46Z

I suspect I missing something really obvious here, but is there a way to programatically create a new catalog?

I've had a look at PROC CATALOG but can't see anything which would create a new one.

(I did try to tag this as Catalogs/PROC Catalog, but apparently these are new tags and I can't create them)

Placeholder Rows/Values from XML libname

2009-11-26T15:52:45Z

I have some XML data which I'm trying to read into SAS 9.1.3 through the XML libname engine. Said XML data looks a lot like this (some may recognise that's it's output from PROC METADATA):

<?xml version="1.0" encoding="windows-1252"?>
<GetMetadataObjects>
  <Reposid>A0000001.AREPOSID</Reposid>
  <Type>Person</Type>
  <Objects>
    <Person Id="AREPOSID.AA000001" Name="User The 1st">
      <Logins/>
    </Person>
    <Person Id="AREPOSID.AA000002" Name="User The 2nd">
      <Logins>
        <Login Id="AREPOSID.AV000001" UserID="AUserID">
          <Domain>
            <AuthenticationDomain Id="AREPOSID.AD000001" Name="DefaultAuth"/>
          </Domain>
        </Login>
        <Login Id="AREPOSID.AV000002" UserID="AlsoUserID">
          <Domain>
            <AuthenticationDomain Id="AREPOSID.AD000002" Name="DummyAuth"/>
          </Domain>
        </Login>
      </Logins>
    </Person>
    <Person Id="AREPOSID.AA000003" Name="Person The 3rd">
      <Logins/>
    </Person>
  </Objects>
  <NS>SAS</NS>
  <Flags>260</Flags>
  <Options>
    <Templates>
      <Person Id="" Name="">
        <Logins Id=""/>
      </Person>
      <Logins>
        <Login/>
      </Logins>
      <Login Id="" Userid="">
        <Domain/>
      </Login>
      <Domain> 
        <AuthenticationDomain/>
      </Domain>
      <AuthenticationDomain Name=""/>
    </Templates>
  </Options>
</GetMetadataObjects>

The above XML is associated with a file reference called xml_out, and I'm using the following code to read it into a SAS dataset:

filename xml_map temp;
data _NULL_;
  file xml_map;
  put 
    '<?xml version="1.0" encoding="windows-1252"?>'
    /   '<SXLEMAP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="TreeMap" version="1.2" xsi:noNamespaceSchemaLocation="http://www.sas.com/xml/schema/sxle12.xsd">'
    /   '    <TABLE name="user_logins">'
    /   '       <TABLE-PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login</TABLE-PATH>'
    /   '        <COLUMN name="UserMetaId">'
    /   '            <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/@Id</PATH>'
    /   '            <TYPE>character</TYPE>'
    /   '            <DATATYPE>string</DATATYPE>'
    /   '            <LENGTH>200</LENGTH>'
    /   '        </COLUMN>'
    /   '        <COLUMN name="UserMetaName">'
    /   '            <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/@Name</PATH>'
    /   '            <TYPE>character</TYPE>'
    /   '            <DATATYPE>string</DATATYPE>'
    /   '            <LENGTH>200</LENGTH>'   
    /   '        </COLUMN>'
    /   '        <COLUMN name="LoginsGroup">'
    /   '            <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/@Id</PATH>'
    /   '            <TYPE>character</TYPE>'   
    /   '            <DATATYPE>string</DATATYPE>'   
    /   '            <LENGTH>200</LENGTH>'
    /   '        </COLUMN>' 
    /   '        <COLUMN name="LoginDomain">'   
    /   '            <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login/Domain/AuthenticationDomain/@Name</PATH>'   
    /   '            <TYPE>character</TYPE>'
    /   '            <DATATYPE>string</DATATYPE>'
    /   '            <LENGTH>200</LENGTH>'
    /   '        </COLUMN>'   
    /   '        <COLUMN name="LoginId">'
    /   '            <PATH syntax="XPath">/GetMetadataObjects/Objects/Person/Logins/Login/@Id</PATH>'
    /   '            <TYPE>character</TYPE>'   
    /   '            <DATATYPE>string</DATATYPE>'   
    /   '            <LENGTH>200</LENGTH>'
    /   '        </COLUMN>'   
    /   '    </TABLE>'
    /   '</SXLEMAP>'
  ;
run;
libname xml_out xml xmlfileref=xml_out xmlmap=xml_map;
data logins_details_test;
  set xml_out.user_logins;
run;

The output data from this procedure looks like this:

UserMetaId         UserMetaName  LoginDomain  LoginId
---------------------------------------------------------------
AREPOSID.AA000002  User The 2nd  DefaultAuth  AREPOSID.AV000001
                                 DummyAuth    AREPOSID.AV000002

Can anybody suggest what I'd need to change in either the xml map, or in the libname statement so that I get either 1 row per login found, or a single row with no login details if no login details are visible? i.e.

UserMetaId         MetaName      LoginDomain  LoginId
---------------------------------------------------------------
AREPOSID.AA000001  User The 1st
AREPOSID.AA000002  User The 2nd  DefaultAuth  AREPOSID.AV000001
AREPOSID.AA000002  User The 2nd  DummyAuth    AREPOSID.AV000002
AREPOSID.AA000003  User The 3rd

Unduplicating records within a SAS dataset

2009-10-20T17:53:51Z

I am new to SAS but have background programming in C++, Stata, SPSS, even JMP.

Our datasets contain multiple duplicates and I need to unduplicate the SAS records/rows within the files using multiple variables, (var1, var2, var3, var4, var5). A unique record is basically with var 1 to var 5 are a multiple key combination to indicate a unique episode of care.

In Stata I would do something like: duplicates drop (var1 var2 var3 var4 var5), force

This would drop multiple records/rows that had the same combination value of var1 to var5

Thanks for any help.

Need help with calculating a column

2009-11-24T11:06:35Z

The data set looks like

Val1    Sum of last 5 (Non Blank)
10      . 
.       10 
20      10 
30      30 
.       60 
40      60 
50      100 
30      150 
.       170 
.       170 
10      170 
20      160 
30      150 
40      130 
.       130

I need to write a code which calculates the second column, which is the sum of last five values present in column1.

Tables not Listed under SQL Server LIBNAME

2009-11-13T14:21:55Z

I've been having this problem for some time (it seems since the initial release of SAS v9).

Steps to Reproduce

I assign a libname to SQL Server (or Oracle -- I haven't verified, but it seems any external DB except for SPDS)
Browse to the LIBNAME by double-clicking in SAS Explorer

Expected Results

I can see all of the database tables

Actual Results

I see a blank LIBREF

Here's the kicker. I know the tables are actually there and available to SAS. If I just write code to access the tables I KNOW are there; everything works fine. I get the data/resutls I expect.

I just can't BROWSE THE LIBNAME.

Any ideas? Am I missing something in the assignment?

This happens with both ODBC and OLEDB (and I think Access to ORACLE) engines by the way.

Coding the value codes and labels along with data type for variables

2009-11-03T00:24:29Z

Is there a good specific resource to show examples of coding to create the following?

The description of the variable/column within the table, example for TERM_TYPE, would be Disposition at discharge.
The value labels for the variable/column for TERM_TYPE such as 1= Appointment not kept within 14 days of enrollment 2=Client terminates treatment without clinical agreement, 3 = Treatment is complete, etc.
Even if the variable TERM_TYPE is a number, 1, 2, 3, .... it is treated as a categorical/nominal variable.

Thanks for your help in transitioning between statistical coding/programming.

Dagan

Building a character string?

2009-10-31T13:09:39Z

Hi, I have the following sample data:

custid 
100 
200 
300 
400 
500 
. 
. 
etc.

I want to build the following string:

where custid in (100, 200, 300, 400, 500.....)

I have SAS 8.21 installed at my location. What is the best way to accomplish this? Thanks.

Help with Report Generation

2009-10-30T12:15:50Z

Hello:

Our office is attempting to edit SAS code that generates course evaluations for faculty at our college. We currently have code that generates individual course evaluation results with the faculty member listed and his/her department on the report page. Additionally, we have code that adds an line for the college total aggregate (as we have a standard evaluation form used across campus) to compare the results of the evaluation for the individual course to the college. What we would like to do is aggregate the department total and have it print to the appropriate page (as the course is linked to the department in which it is taught). This would be an additional line on the individual report pages for each course that compares that course total to the department total. We are comfortable with the summation of the information, but where we are struggling is understanding summing it by department and getting it to print to the appropriate page (i.e. the department in which the course is taught). For example, if I have a course evaluation report page for ART 101, we'll need a department total for the department of ART. We appreciate any help you can give.

Daniel

See below for our SAS code to generate individual course reports:

OPTIONS nonumber nodate;
   data courses         ;
    infile 'l:\file';
       input
                major  $ 1-1
                grade  $ 2-2
                status $ 3-3
                gpa    $ 4-4
                sex    $ 5-5
                instr  $ 6-6
                know   $ 7-7
                course1 $ 8-8
                CRN_key $ 9-13
    ;
    if major = "*" then major = ' ';
    if grade = "**" then grade = ' ';
    if status = "*" then status = ' ';
    if gpa = "*" then gpa = ' ';
    if sex = "*" then sex = ' ';
    if instr = "*" then instr = ' ';
    if know = "*" then know = ' ';
    if course1 = "*" then course1 = ' ';
    run;
/*
proc freq data=courses;
  tables major grade status gpa sex instr know course1;
  tables major*crn_key / noprint out=outer1;
  tables status*crn_key / noprint out=outer2;
run;
proc print data=outer1 noobs;
run;
proc print data=outer2 noobs;
run;
*/

proc sort data=courses;
  by crn_key;
run;

data banner;
  set newdata.coursespring09;
  if dept_code not in ('QQ',' ');
run;
proc sort;
  by crn_key;
run;

data combine;
  merge courses (in=ineval) banner (in=inbanner);
  by crn_key;
/*Added for course that had 2 instructors
  8888 was the course number given to this section*/
  if crn_key = '07000' then dept_code = 'NURS';
  if crn_key = '07100' then dept_code = 'NURS';
  *if crn_key = '8894' then dept_code = 'NURS';
  *if crn_key = '8895' then dept_code = 'NURS';
if (ineval and inbanner) or crn_key in ('07000','07100','8894','8895');
run;

data lookie;
  set combine;
  if dept_code = ' ' or subj_code = ' ';
run;

proc print;
run;

data lookover;
  set combine;
  length course $13.;
  course = subj_code||crse_number||seq_number_key;
  if primary_instructor_id = '26' then primary_instructor_last_name = "NAME.";
  if primary_instructor_id = '25' then primary_instructor_last_name = "NAME.";
  if primary_instructor_id = '42' then primary_instructor_last_name = "NAME2.";
  if primary_instructor_id = '72' then primary_instructor_last_name = "NAME2.";
  if primary_instructor_id = '60' then primary_instructor_last_name = "NAME3.";
  if primary_instructor_id = '49' then primary_instructor_last_name = "NAME4.";
/*adding in the last name for the section that was added*/
 if crn_key = '07000' then do;
    primary_instructor_last_name = "NAM";
    course = 'NURS485  01*';
  end;
  if crn_key = '07100' then do;
    primary_instructor_last_name = "NAM";
    course = 'NURS410  01*';
  end;
  *if crn_key = '8894' then do;
   * primary_instructor_last_name = "NAM1";
    *course = 'NURS323  01*';
*  end;
 * if crn_key = '8895' then do;
  *  primary_instructor_last_name = "NAM2";
    *course = 'NURS326  01*';
  *end;
run;

proc sort;
  by course;
run;

data combine2;
  set lookover;
  by course;
  if first.course then do;
   count = 0;
   in1 = 0;
   in2 = 0;
   in3 = 0;
   in4 = 0;
   in5 = 0;
   in6 = 0;
   kn1 = 0;
   kn2 = 0;
   kn3 = 0;
   kn4 = 0;
   kn5 = 0;
   kn6 = 0;
   co1 = 0;
   co2 = 0;
   co3 = 0;
   co4 = 0;
   co5 = 0;
   co6 = 0;

   maj_cnt0 = 0;
   maj_cnt1 = 0;
   maj_cnt2 = 0;
   maj_cnt3 = 0;
   maj_cnt4 = 0;
   sts_cnt0 = 0;
   sts_cnt1 = 0;
   sts_cnt2 = 0;
   sts_cnt3 = 0;
   sts_cnt4 = 0;

  end;
  count+1;
  if instr = '1' then in1+1;
  if instr = '2' then in2+1;
  if instr = '3' then in3+1;
  if instr = '4' then in4+1;
  if instr = '5' then in5+1;
  if instr = '6' then in6+1;
  if know = '1' then kn1+1;
  if know = '2' then kn2+1;
  if know = '3' then kn3+1;
  if know = '4' then kn4+1;
  if know = '5' then kn5+1;
  if know = '6' then kn6+1;
  if course1 = '1' then co1+1;
  if course1 = '2' then co2+1;
  if course1 = '3' then co3+1;
  if course1 = '4' then co4+1;
  if course1 = '5' then co5+1;
  if course1 = '6' then co6+1;

  if major in ('0',' ') then maj_cnt0+1;
  if major = '1'        then maj_cnt1+1;
  if major = '2'        then maj_cnt2+1;
  if major = '3'        then maj_cnt3+1;
  if major = '4'        then maj_cnt4+1;
  if status in ('0',' ','5')then sts_cnt0+1;
  if status = '1'       then sts_cnt1+1;
  if status = '2'       then sts_cnt2+1;
  if status = '3'       then sts_cnt3+1;
  if status = '4'       then sts_cnt4+1;

  if last.course;
run;

data combine3;
  set combine2;
  count_i = sum(in1,in2,in3,in4,in5,in6);
  count_k = sum(kn1,kn2,kn3,kn4,kn5,kn6);
  count_c = sum(co1,co2,co3,co4,co5,co6);

  count_m = sum(maj_cnt0,maj_cnt1,maj_cnt2,maj_cnt3,maj_cnt4);
  count_s = sum(sts_cnt0,sts_cnt1,sts_cnt2,sts_cnt3,sts_cnt4,sts_cnt5);

  su_i = in1/count_i;
  vg_i = in2/count_i;
  gd_i = in3/count_i;
  sa_i = in4/count_i;
  us_i = in5/count_i;
  vu_i = in6/count_i;
  su_k = kn1/count_i;
  vg_k = kn2/count_k;
  gd_k = kn3/count_k;
  sa_k = kn4/count_k;
  us_k = kn5/count_k;
  vu_k = kn6/count_k;
  su_c = co1/count_c;
  vg_c = co2/count_c;
  gd_c = co3/count_c;
  sa_c = co4/count_c;
  us_c = co5/count_c;
  vu_c = co6/count_c;

  major0 = maj_cnt0/count_m;
  major1 = maj_cnt1/count_m;
  major2 = maj_cnt2/count_m;
  major3 = maj_cnt3/count_m;
  major4 = maj_cnt4/count_m;
  status0 = sts_cnt0/count_s;
  status1 = sts_cnt1/count_s;
  status2 = sts_cnt2/count_s;
  status3 = sts_cnt3/count_s;
  status4 = sts_cnt4/count_s;

  format su_i--vu_c percent6. instrctr $12.
         major0--major4 percent6. status0--status4 percent6.;
  instrctr = primary_instructor_last_name;
  mrgorder = 1;
run;



proc summary data=combine2;
  var count in1--co6;
  output out = summtot
         sum = scount sin1 sin2 sin3 sin4 sin5 sin6
                      skn1 skn2 skn3 skn4 skn5 skn6
                      sco1 sco2 sco3 sco4 sco5 sco6;
run;
*proc print data=summtot;
*run;

data colltot;
  set summtot;
  mrgorder = 1;
  count_i2 = sum(sin1,sin2,sin3,sin4,sin5,sin6);
  count_k2 = sum(skn1,skn2,skn3,skn4,skn5,skn6);
  count_c2 = sum(sco1,sco2,sco3,sco4,sco5,sco6);
  su_i2 = sin1/count_i2;
  vg_i2 = sin2/count_i2;
  gd_i2 = sin3/count_i2;
  sa_i2 = sin4/count_i2;
  us_i2 = sin5/count_i2;
  vu_i2 = sin6/count_i2;
  su_k2 = skn1/count_k2;
  vg_k2 = skn2/count_k2;
  gd_k2 = skn3/count_k2;
  sa_k2 = skn4/count_k2;
  us_k2 = skn5/count_k2;
  vu_k2 = skn6/count_k2;
  su_c2 = sco1/count_c2;
  vg_c2 = sco2/count_c2;
  gd_c2 = sco3/count_c2;
  sa_c2 = sco4/count_c2;
  us_c2 = sco5/count_c2;
  vu_c2 = sco6/count_c2;
run;
/*Added for the Course title of added course*/
data combine4;
  merge combine3 colltot;
  if crn_key = '8892' then title = 'COURSE1';
  if crn_key = '8893' then title = 'COURSE1';
  if crn_key = '8894' then title = 'COURSE2';
  if crn_key = '8895' then title = 'COURSE3';
  by mrgorder;
run;


proc sort;
  by course;
run;

data _null_;
 retain a 1 b 15 c 28 d 30 e 35 f 41 g 47 h 53
        i 59 j 65 k 71 l 72 m 78 n 84 o 90 p 96
        q 102 r 108 s 114 t 116 u 121 v 127
        w 133 x 139 y 145 z 151 zz 157;
 file print;
 set combine4;
 by course;
 format su_i--vu_c percent6. count_i count_k count_c count_i2 count_k2 count_c2 4.
        su_i2--vu_c2 percent6.;
 put _page_ /
     #1 @a +60 "COLLEGE AB"
     #3 @a +76 'Spring 2009'
     #4 @a +64 'Course Evaluation Section Summary'
     #7 @a course @c title @m instrctr
     #10 @d '__________Instructor Ratings____________'
         @l '__________ Knowledge Gained ____________'
         @t '____________Course Ratings______________'
     #11 ' '
     #12 @c 'Count'
         @e '  SU' @f '  VG' @g '  GD' @h '  SA' @i '  US' @j '  VU'
         @l 'Count'
         @m '  SU' @n '  VG' @o '  GD' @p '  SA' @q '  US' @r '  VU'
         @t 'Count'
         @u '  SU' @v '  VG' @w '  GD' @x '  SA' @y '  US' @z '  VU'
     #13 ' ' //    
     @a 'Cumulative Results:' @c '|' @k '|' @s '|' @zz '|'/
                              @c '|' @k '|' @s '|' @zz '|'/
     @a+2 'Section Percent'   @c '|'
     @d count_i 4.
     @e su_i percent6.
     @f vg_i percent6.
     @g gd_i percent6.
     @h sa_i percent6.
     @i us_i percent6.
     @j vu_i percent6.
     @k '|'
     @l count_k 4.
     @m su_k
     @n vg_k percent6.
     @o gd_k percent6.
     @p sa_k percent6.
     @q us_k percent6.
     @r vu_k percent6.
     @s '|'
     @t count_c 4.
     @u su_c percent6.
     @v vg_c percent6.
     @w gd_c percent6.
     @x sa_c percent6.
     @y us_c percent6.
     @z vu_c percent6.
     @zz '|' /
     @c  '|' @k '|' @s '|' @zz '|' /
     @a+2 'College Percent' @c '|'
     @d count_i2 4.
     @e su_i2 percent6.
     @f vg_i2 percent6.
     @g gd_i2 percent6.
     @h sa_i2 percent6.
     @i us_i2 percent6.
     @j vu_i2 percent6.
     @k '|'
     @l count_k2 4.
     @m su_k2
     @n vg_k2 percent6.
     @o gd_k2 percent6.
     @p sa_k2 percent6.
     @q us_k2 percent6.
     @r vu_k2 percent6.
     @s '|'
     @t count_c2 4.
     @u su_c2 percent6.
     @v vg_c2 percent6.
     @w gd_c2 percent6.
     @x sa_c2 percent6.
     @y us_c2 percent6.
     @z vu_c2 percent6.
     @zz '|' /
     @c  '|' @k '|' @s '|' @zz '|' ///

     @a +70  'General Information' //
     @a +70  '-------------------' ///
     @a +30  'Percent of Students for Whom Course is:' @a +98  'Student Classification:' //
     @a+32 major1 @a+38 'In major and required' @a +100 status1 @a+106 'First-Year' /
     @a+32 major2 @a+38 'In major and not required' @a +100 status2 @a+106 'Sophomore' /
     @a+32 major3 @a+38 'Not in major but required' @a +100 status3 @a+106 'Junior'  /
     @a+32 major4 @a+38 'Not in major, not required' @a +100 status4 @a+106 'Senior' /
     @a+32 major0 @a+38 'Not specified' @a +100 status0 @a+106 "Other/Not specified" //////////
     @a 'Notes:  SU = superior, VG = very good, GD = good, SA = satisfactory, US = unsatisfactory, VU = very unsatisfactory'/
     @a '        Percents may not add to 100 due to rounding.                                                              ';
  title ' ';
run;

Help with variable calculation over multiple observations?

2009-10-25T09:55:22Z

type                    number  VISIT   date 
AMOUNT DISPENSED        0       1 
AMOUNT DISPENSED        18      2       2007-08-20 
AMOUNT DISPENSED        18      2       2007-08-20 
AMOUNT DISPENSED        18      2       2007-08-20 
AMOUNT DISPENSED        0       3 
AMOUNT DISPENSED        0       4 
AMOUNT DISPENSED        0       5 
AMOUNT DISPENSED        18      6       2007-08-26 
AMOUNT DISPENSED        18      6       2007-08-26 
AMOUNT RETURNED         18      6       2007-08-26 
AMOUNT RETURNED         20      6       2007-08-26 
AMOUNT LOST             0       6       2007-08-26 
AMOUNT LOST             1       6       2007-08-26 
AMOUNT DISPENSED        0       7 
AMOUNT DISPENSED        0       8 
AMOUNT RETURNED         0       8       2008-09-17 
AMOUNT RETURNED         14      8       2008-09-17 
AMOUNT DISPENSED        16      10      2008-09-09 
AMOUNT DISPENSED        16      10      2008-09-09 
AMOUNT RETURNED         0       10      2008-09-09 
AMOUNT RETURNED         7       10      2008-09-09 
AMOUNT LOST             0       10      2008-09-09 
AMOUNT LOST             1       10      2008-09-09 
AMOUNT MISSED           0       10      2008-09-09 
AMOUNT MISSED           2       10      2008-09-09

I need to calculate the amount of dose taken using the following formula

Dose Taken = (total dose  dispensed at previous visit) -  
             (total Dose returned at present visit ) -  
             (minus total dose  lost)

I would appreciate if anyone could provide me a piece of code to do the above calculation.

Building an Array from values in a Variable?

2009-10-25T09:48:42Z

I have normalized assessment data, with items nested within students. I would like to define an array based on the item numbers in the variable ITEM in my data file.

Current values are

i1, i2...ik.

Is there a way to create an array reference based on the unique values in my ITEM field?

Note, I'm searching for a solution flexible enough that can handle the identification of different arrays across different assessments. In other words, the first assessment may have items i1 - i20, the second assessment may have items i1 - i30.