topic Re: Look up in loops / can not add in the additional path to look up for more data in SAS Data Management

Look up in loops / can not add in the additional path to look up for more data

Cruise — Thu, 29 Jun 2017 23:43:56 GMT

My data is labeled after year created and saved in two separate folders with path "..\out" and "....\out\recent" (defined better below). I have no privilige to move files even though I tried to put all data in the same folder. Current look up works for data after 2016. But I need to use earlier data as well.

What I'm trying to achieve is to direct the loop to pull out data from "libname...\Out" if data is before 2015, else pull out from "libname...Out\recent" if data is after 2016.

I simply added below chunk in the code in the box with no success. Log is "NOTE: The data set WORK.ALL_OUT_OTHER has 0 observations and 11 variables". How is it possible? Macro as is before I add another libname returns about 200 cases from "libname...Out\recent". But no observations after additional path is added in to look up for more data? Using 9.4.

%if &year <= 2015 %then
%do;
libname read_in "C:\Data\SPARCSdeidentified\&type";

%if &type=Int %then %let file=&type._s_;
%else;
%if &type=Out %then %let file=&type._s_p_;
%end;

libname read_in "C:\Data\SPARCSdeidentified\Int";
libname read_op "C:\Data\SPARCSdeidentified\Out\recent";

%macro loop(case);

%global case1;
%let case1=&case;

%if &case=diseases                        %then %let condition = &ac;

proc datasets noprint;
 delete all_&type._other;
run;
 
%do year = 2015 %to 2017; 
%if &year >= 2016 %then 
  %do; 
    libname read_in "C:\Data\SPARCSdeidentified\&type\recent";
     %if &type=Int %then %let file=&type._s_;
	  %else;
     %if &type=Out %then %let file=&type._s_p_;
   %end; 

data temp(compress=yes); set read_in.&file&year;
icd_code = catx('*','*',Code, of Other_Code:); 

if prxmatch("m/&condition/oi",icd_code) > 0; 
source="Int";
run;

proc append base=all_&type._other(compress=yes) data=temp force;
run;

%end;

%mend loop;

%let type=Int;
%loop(diseases);

%let type=Out;
%loop(Other);

Thanks for help.

Re: Look up in loops / can not add in the additional path to look up for more data

Reeza — Fri, 30 Jun 2017 00:12:41 GMT

Why not make the libname the IF condition. Then you can use the same code for the test of the stuff.

Also, why not make Type a parameter in your macro.

What does your hardcoded working code look like?

Re: Look up in loops / can not add in the additional path to look up for more data

Cruise — Fri, 30 Jun 2017 00:31:43 GMT

@Reeza

Below is initial code where lookup pulls cases from the &ac list of diagnosis. Attached in notepad is hardcode only for 2016 and 2017. In this case i have more than 3 years so would be tedious and exhausting. I thought of making type a parameter but i was afraid to distort the standard look up code.

data icd(drop=icd10 rename=x=icd); set icd;
  icd_10_original=icd10;
 x="[*]"||icd10; run;

proc sql noprint;
   select  icd
     into :ac separated by '|'
     from  icd;
quit;

%put &ac;

options compress=yes;
%let x=:Code_1 - :Code_24;

Re: Look up in loops / can not add in the additional path to look up for more data

Cruise — Fri, 30 Jun 2017 00:36:40 GMT

hard to make type a parameter because I have two solutions for type after different folders in the path such as Int and Out

Re: Look up in loops / can not add in the additional path to look up for more data

Cruise — Fri, 30 Jun 2017 01:52:40 GMT

I just put all my data together in the same folder. Just run below and still got no observations in final output.

%do year = 2015 %to 2017; 
 %do; 
    libname read_in "D:\data\&type";
     %if &type=Int %then %let file=&type._s_;
	  %else;
     %if &type=Out %then %let file=&type._s_prime_;
   %end;

Log says:

NOTE: The file WORK.ALL_OUT_OTHER (memtype=DATA) was not found, but appears on a DELETE
statement.
NOTE: Libref READ_IN was successfully assigned as follows:
Engine: V9
Physical Name: D:\\Out

NOTE: PROCEDURE DATASETS used (Total process time):
real time 1.44 seconds
cpu time 0.34 seconds

NOTE: There were 67986 observations read from the data set READ_IN.OUT_2015.
WHERE (patient_state='NY') and ('2015'<=SUBSTR(LEFT(patient_birth_date), 1, 4)) and
patient_county_code in (2, 4, 6, 14, 18, 26, 29, 33, 34, 37, 39, 55, 56, 58) and
(SUBSTR(LEFT(patient_birth_date), 1, 4)<='2017');
NOTE: The data set WORK.TEMP has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 1:57.57
cpu time 34.01 seconds

NOTE: Appending WORK.TEMP to WORK.ALL_OUT_OTHER.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.ALL_OUT_OTHER has 0 observations and 11 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds

NOTE: Libref READ_IN was successfully assigned as follows:
Engine: V9
Physical Name: D:\..\Out
WARNING: Apparent symbolic reference CONDITION not resolved.

NOTE: There were 184448 observations read from the data set READ_IN.OUT_2016.
WHERE (patient_state='NY') and ('2015'<=SUBSTR(LEFT(patient_birth_date), 1, 4)) and
patient_county_code in (2, 4, 6, 14, 18, 26, 29, 33, 34, 37, 39, 55, 56, 58) and
(SUBSTR(LEFT(patient_birth_date), 1, 4)<='2017');
NOTE: The data set WORK.TEMP has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 2:33.67
cpu time 46.76 seconds

NOTE: Appending WORK.TEMP to WORK.ALL_OUT_OTHER.
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: 0 observations added.
NOTE: The data set WORK.ALL_OUT_OTHER has 0 observations and 11 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

NOTE: Libref READ_IN was successfully assigned as follows:
Engine: V9
Physical Name: D:\sparcsdoh\task3\task3data\SPARCSdeidentified\Outpatient
WARNING: Apparent symbolic reference CONDITION not resolved.

NOTE: There were 64071 observations read from the data set READ_IN.OUT_S_PRIME_2017.
WHERE (patient_state='NY') and ('2015'<=SUBSTR(LEFT(patient_birth_date), 1, 4)) and
patient_county_code in (2, 4, 6, 14, 18, 26, 29, 33, 34, 37, 39, 55, 56, 58) and
(SUBSTR(LEFT(patient_birth_date), 1, 4)<='2017');
NOTE: The data set WORK.TEMP has 0 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 39.43 seconds
cpu time 9.90 seconds

Any idea? Thanks

Re: Look up in loops / can not add in the additional path to look up for more data

Patrick — Fri, 30 Jun 2017 06:06:45 GMT

@Cruise

I believe for your latest issue the Condition doesn't work.

WARNING: Apparent symbolic reference CONDITION not resolved.

For accessing tables from different folders:

You can define a single libref for multiple paths/folders (concatenated library). As long as the tables in the folders have different names you then can access these tables as if they would reside in a single folder.

If your tables follow a naming convention then you can read them using the column modifier.

Code sample:

libname source ('c:\temp', 'c:\test');

data want;
  length _intbl sourceTable $32;
  set source.OUT_20: indsname=_intbl;
  sourceTable=_intbl;
run;