topic Re: Mashing datasets with different varaibles in SAS Programming

Mashing datasets with different varaibles

BTAinRVA — Thu, 27 Dec 2018 15:13:48 GMT

Hello Everybody!

I have one dataset that has diagnoses 1 thru 8 (DX1-DX8) and a bunch of others that have DX1-DX25. I'm trying to use one macro to process all these datasets but can't quite figure out how to do it. Instead I've duplicated the macro so that one will process the one file and the other processes the others, then use Proc Append to mash them together. Just wondering if there is a more elegant way to do this using one macro?

Thanks,

Brian

Re: Mashing datasets with different varaibles

jklaverstijn — Thu, 27 Dec 2018 15:25:56 GMT

Hi Brian,

The ultimate answer will probably depend on what you mean by "processing". But when assuming you mean you want to do stuff to a dataset and not be hindered by some missing variables then the following code would first add them (and obviously assign them missing values). It does assume (again) that there is a known datasets that has DX1-DX25:

data work.processthisone / view=work.processthisone;
   if 0 the set datasetwithallvars;
   set nextdataset;
run;

/* Go and do your processing on work.processthisone */

The code uses a view to avoid having to go through all data just to change the dataset descriptor. The "if 0" construct avoids reading the data from the template dataset at run-time while the variable definitions are imported at compile-time.

Hope this helps,

-- Jan.

Re: Mashing datasets with different varaibles

Tom — Thu, 27 Dec 2018 15:33:07 GMT

It is probably possible to write SAS code (without macro logic at all) that could process both. For example this code would work. It would just end up making extra empty variables for the source data that started with only 8.

data want ;
  set have ;
  array dx dx1-dx25;
run;

You could just tell your macro how many variables to process.

%macro my_marcro(dsn=,ndx=);
....
array dx (&ndx) $10 ;
....
%mend ;
%my_macro(dsn=outpatient,ndx=8);
%my_macro(dsn=inpatient,ndx=25);

Or you could let your macro figure out how many DX variable you have.

proc contents noprint data=&dsn out=contents ; run;
proc sql noprint;
  select name into :dx_list separated by ' '
  from contents where upcase(name) like 'DX%' 
  ;
quit;
....
array dx &dx_list ;
....

Re: Mashing datasets with different varaibles

BTAinRVA — Thu, 27 Dec 2018 16:06:51 GMT

Jan,

Thanks for the illuminating reply! I learned something today.

Unfortunately I'm not sure it would work in my case. The macro opens up datasets stored in different directories, processes them, then appends them, kind of like this:

%macro bta(fy);
libname bta "/bta/tdata/fy&fy.";

data tdata&fy.;
set bta.teddy ;

if tflflag = "U" and place="26" then delete;

** Remove any duplicate records **;
proc sort data=tdata&fy. nodupkey;
by _all_;
run;

** Append to one data set **;
proc append data = tdata&fy. base = crc.tdata;
run;

** Clean up **;
proc datasets memtype=data lib=work nolist;
delete tdata&fy.;
quit;
run;

%mend bta;

%bta(03);
%bta(04);
%bta(05);
%bta(06);
%bta(07);
%bta(08);

The 03 dataset has DX1-DX8 while the others have DX1-DX25.

Re: Mashing datasets with different varaibles

Tom — Thu, 27 Dec 2018 17:42:50 GMT

The issue you are describing would actually not cause any trouble for the code you posted.

Here is an example that proves it.

135   data base (keep=dx1-dx10) add (keep=dx1-dx5 );
136    array dx (10) $10 ;
137   run;

NOTE: The data set WORK.BASE has 1 observations and 10 variables.
NOTE: The data set WORK.ADD has 1 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds


138
139   proc append base=base data=add ;
140   run;

NOTE: Appending WORK.ADD to WORK.BASE.
WARNING: Variable dx6 was not found on DATA file.
WARNING: Variable dx7 was not found on DATA file.
WARNING: Variable dx8 was not found on DATA file.
WARNING: Variable dx9 was not found on DATA file.
WARNING: Variable dx10 was not found on DATA file.
NOTE: There were 1 observations read from the data set WORK.ADD.
NOTE: 1 observations added.
NOTE: The data set WORK.BASE has 2 observations and 10 variables.

If you want to get rid of the WARNING messages you could just add the extra variables when you make the copy of the data.

%macro bta(fy);
libname bta "/bta/tdata/fy&fy.";

data tdata ;
  set bta.teddy ;
  if tflflag = "U" and place="26" then delete;
  array dx (25) ;
run;

** Remove any duplicate records **;
proc sort data=tdata nodupkey;
  by _all_;
run;

** Append to one data set **;
proc append data = tdata base = crc.tdata;
run;

** Clean up **;
proc delete data=tdata;
run;

%mend bta;

Re: Mashing datasets with different varaibles

BTAinRVA — Thu, 27 Dec 2018 20:19:51 GMT

Tom,

Thanks for the reply! Yours is probably the correct answer but I'm having trouble implementing it as the DX1-DX8 in the first file have different length than those in the other files and so the Proc Append fails to add DX9-DX25.

Brian

Re: Mashing datasets with different varaibles

Tom — Thu, 27 Dec 2018 20:43:02 GMT

When using PROC APPEND make sure to define the BASE dataset with the attributes you want before appending any the actual datasets.
You can use the FORCE option, but that just means the step will run even if the new dataset has extra variables, but it does not mean that the extra variables are actually added to the BASE dataset. Instead the extra variables are ignored.