CODEWITHSUNDEEP
sassumsas-macro
Brian Bartle
Brian Bartle

Reputation: 53

Iteratively adding to merged SAS dataset

I have 18 separate datasets that contain similar information: patient ID, number of 30-day equivalents, and total day supply of those 30-day equivalents. I've output these from a dataset that contains those 3 variables plus the medication class (VA_CLASS) and the quarter it was captured in (a total of 6 quarters).

sample data

Here's how I've created the 18 separate datasets from the snip of the dataset shown above:

%macro rx(class,num);
proc sql;
create table dm_sum&clas._qtr&num as select PatID,
sum(equiv_30) as equiv_30_&class._&num
from dm_qtrs
where va_class = "HS&class" and dm_qtr = &qtr
group by 1;
quit;
%mend;
%rx(500,1);
%rx(500,2);
%rx(500,3);
%rx(500,4);
%rx(500,5);
%rx(500,6);
%rx(501,1);

and so on...

I then need to merge all 18 datasets back together by PatID and what I'd like to do is iteratively add the next dataset created to the previous, as in, add dataset dm_sum_500_qtr3 to a file that already contains the results of dm_sum_500_qtr1 & dm_sum_500_qtr1.

Thanks for looking, Brian

Upvotes: 0

Views: 137

Answers (1)

Richard
Richard

Reputation: 27508

In the macro append the created data set to it an accumulator data set. Be sure to delete it before starting so there is a fresh accumulation. If the process is run at different times (like weekly or monthly) you may want to incorporate a unique index to prevent repeated appendings. If you are stacking all these sums, the create table should also select va_class and dm_qtr

%macro (class, num, stack=perm.allClassNumSums);
  proc sql; create table dm_sum&clas._qtr&num as … ;
  proc append force base=perm.allClassNumSums data=dm_sum&clas._qtr#
  run;
%mend;

proc sql;
  drop table perm.allClassNumSums;
%rx(500,1)
%rx(500,2)
%rx(500,3)
%rx(500,4)
%rx(500,5)
…

A better approach might be a single query with an larger where, and leave the class and qtr as categorical variables. Your current approach is moving data (class and qtr) into metadata (column names). Such a transformation makes additional downstream processing more difficult.

Proc TABULATE or REPORT can be use a CLASS statement to assist the creation of output having category based columns. These procedures might even be able to work directly with the original data set and not require a preparatory SQL query.

proc sql;
  create table want as
  select 
    PatID, va_class, dm_qtr,
    sum(equiv_30) as equiv_30_sum
    from dm_qtrs
    where catx(':', va_class, dm_sqt) in 
    (
      'HS500:1'
      'HS500:2'
      'HS500:3'
      … 
      'HS501:1'
    )
    group by PatID, va_class, dm_qtr;
quit;

Upvotes: 0

Related Questions