DATA Step, Macro, Functions and more

Macro for inputting and outputting different files

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Macro for inputting and outputting different files

[ Edited ]

Thanks everyone for reading! So, I have written a SAS code using Macro variables (%let statement for file location, date, output location) where the code reads input csv files using infile statement, uses first. and other manipulation before joining datasets and producing three different files which are exported using the proc export three times. There are four such codes used which are similar in nature except that the input files are different and manipulation varies a bit.

I'm hoping to write a macro which encompasses all these 4 seperate coded into one. Meaning depending upon the input file, macro could direct SAS to run the relevant code. Below is the code(s) i have written. The other codes are similar except that files have different/fewer variables and sometimes 2 and not 3 final output files are created.

%LET outroot = C:\SAS\input_files\Readmission_MaineCare2015q2.csv;
%LET MUSKIE  = C:\SAS\input_files\BHP_PANEL_VHP.csv ;
%LET MMYY = JULY16 ;

/* reading Readmit csv file into SAS */

DATA devsas.ATT_READMIT&MMYY;
INFILE "&outroot" delimiter = ',' MISSOVER DSD FIRSTOBS=2 lrecl=1000;
INFORMAT ORID $20.; INFORMAT Facility_Event_Number $20.; INFORMAT PROVIDER $20.; INFORMAT ebm_name $200.; INFORMAT CASE_ID $20.; INFORMAT RULE_ID $10.; INFORMAT Case_name $10.; 
INFORMAT result_flag $10. ; INFORMAT task_name $10.; INFORMAT  admit_date mmddyy10.; INFORMAT discharge_date mmddyy10.;
INPUT ORID $ admit_date Facility_Event_Number $ PROVIDER $ discharge_date ebm_name $ CASE_ID $ RULE_ID $ Case_name $ result_flag $ task_name $;
FORMAT ADMIT_DATE MMDDYY10.; FORMAT DISCHARGE_DATE MMDDYY10.;
RUN;

/* reading another input file */

Data devsas.BHP_PANEL_VHP_&MMYY;

INFILE "&MUSKIE" delimiter = ',' MISSOVER DSD FIRSTOBS=2 lrecl=5000;

INFORMAT medicaid_Id $20.; 	INFORMAT pay_to_NPI $20. ;	INFORMAT pay_to_name $100. ; INFORMAT srvLoc_NPI $25.; INFORMAT	srvLoc_Name $100.; 	INFORMAT prov_typ_Cd $20. ;
informat stage $10. ; INFORMAT start_Dt mmddyy10.;	INFORMAT end_Dt mmddyy10.; Informat term_days BEST32. ; INFORMAT term_months BEST32.; INFORMAT new_end_dt mmddyy10.; 
INFORMAT Mth_Start_Dt mmddyy10.; INFORMAT Mth_End_Dt mmddyy10.; INFORMAT Mth_Member_Month BEST32.; INFORMAT Qtr_Start_DT mmddyy10.; INFORMAT Qtr_End_DT mmddyy10.; INFORMAT ttlQ1_2014 BEST32.;
INFORMAT ttlQ2_2014	BEST32.; INFORMAT ttlQ3_2014 BEST32.; INFORMAT ttlQ4_2014 BEST32.; INFORMAT Q1_2014_2Day BEST32.; INFORMAT Q2_2014_2Day BEST32.;  INFORMAT Q3_2014_2Day BEST32.; INFORMAT Q4_2014_2Day BEST32.;
INFORMAT Q1_2014 BEST32.; INFORMAT Q2_2014 BEST32.; INFORMAT Q3_2014 BEST32.; INFORMAT Q4_2014 BEST32.; INFORMAT Q1_2015 BEST32.; INFORMAT Q2_2015 BEST32.; INFORMAT Q3_2015 BEST32.;
INFORMAT Q4_2015 BEST32.; INFORMAT Q1_2016 BEST32.; INFORMAT Q2_2016 BEST32.;  INFORMAT Q3_2016 BEST32.;  INFORMAT Q4_2016 BEST32.;  INFORMAT perMonth BEST32. ;

INPUT medicaid_Id $	pay_to_NPI $ pay_to_name $ srvLoc_NPI $	srvLoc_Name $ prov_typ_Cd $	stage $	start_Dt end_Dt	term_days term_months new_end_dt Mth_Start_Dt Mth_End_Dt Mth_Member_Month
Qtr_Start_DT Qtr_End_DT	ttlQ1_2014 ttlQ2_2014 ttlQ3_2014 ttlQ4_2014	Q1_2014_2Day Q2_2014_2Day Q3_2014_2Day Q4_2014_2Day	Q1_2014	Q2_2014	Q3_2014	Q4_2014	Q1_2015	Q2_2015	Q3_2015	Q4_2015
Q1_2016	Q2_2016	Q3_2016	Q4_2016	perMonth;

FORMAT start_Dt mmddyy10.;	FORMAT end_Dt mmddyy10.; FORMAT new_end_dt mmddyy10.; FORMAT Mth_Start_Dt mmddyy10.; FORMAT Mth_End_Dt mmddyy10.; FORMAT Qtr_Start_DT mmddyy10.; FORMAT Qtr_End_DT mmddyy10.;

RUN;
/* creating output for those patients who were members during this period */

Proc SQL;
Create table devsas.ATT_readmit_dateMatch_&MMYY

as select  a.ORID
     , a.admit_date
     , a.Facility_Event_Number
     , a.PROVIDER
     , a.discharge_date
      ,a.ebm_name
     , a.CASE_ID
     , a.RULE_ID
     , a.Case_name
     , a.result_flag
     , a.task_name     
     , b.pay_to_NPI
     , b.pay_to_name
     , b.srvLoc_NPI
     , b.srvLoc_Name
     , b.prov_typ_Cd
     , b.stage
     , b.start_Dt    
     , b.new_end_dt  
from  devsas.READMIT_july a
left join devsas.BHP_PANEL_VHP b
on a.ORID = b.medicaid_Id
where a.admit_date >= b.start_Dt
  and a.admit_date <= b.new_end_Dt
  and b.stage not in ('CCT')
order by a.ORID, a.admit_date
;
run;

/* creating output for those members which have longest term days */


proc sort data =devsas.BHP_PANEL_VHP_&MMYY out = devsas.BHP_PANEL_VHP_&MMYY; 
by medicaid_Id descending term_days 
;
run;


data devsas.BHP_PANEL_VHP__&MMYY;
set devsas.BHP_PANEL_VHP_&MMYY;
by medicaid_id ;
if first.medicaid_id;
run;

proc sql;
create table devsas.ATT_readmit_longestDays_&MMYY as 
	
select a.ORID
     , a.admit_date
     , a.Facility_Event_Number
     , a.PROVIDER
     , a.discharge_date
      ,a.ebm_name
     , a.CASE_ID
     , a.RULE_ID
     , a.Case_name
     , a.result_flag
     , a.task_name     
     , b.pay_to_NPI
     , b.pay_to_name
     , b.srvLoc_NPI
     , b.srvLoc_Name
     , b.prov_typ_Cd
     , b.stage
     , b.start_Dt    
     , b.new_end_dt  

from  devsas.ATT_READMIT&MMYY a
left join devsas.BHP_PANEL_VHP__&MMYY b
on a.ORID = b.medicaid_Id
order by a.ORID, a.admit_date
;
run;

/* creating output for those members which ARE MOST RECENT */


proc sort data =devsas.BHP_PANEL_VHP_&MMYY out = devsas.BHP_PANEL_VHP_recent_&MMYY; 
by medicaid_Id descending new_end_dt 
;
run;

data devsas.BHP_PANEL_VHP_recent_&MMYY;
set devsas.BHP_PANEL_VHP_recent_&MMYY;
by medicaid_id ;
if first.medicaid_id;
run;


proc sql;
create table devsas.ATT_readmit_mostRecent_&MMYY as 
	
select a.ORID
     , a.admit_date
     , a.Facility_Event_Number
     , a.PROVIDER
     , a.discharge_date
      ,a.ebm_name
     , a.CASE_ID
     , a.RULE_ID
     , a.Case_name
     , a.result_flag
     , a.task_name     
     , b.pay_to_NPI
     , b.pay_to_name
     , b.srvLoc_NPI
     , b.srvLoc_Name
     , b.prov_typ_Cd
     , b.stage
     , b.start_Dt    
     , b.new_end_dt  

from  devsas.ATT_READMIT&MMYY a
left join devsas.BHP_PANEL_VHP_recent_&MMYY b
on a.ORID = b.medicaid_Id
order by a.ORID, a.admit_date
;
run;




/* creating output files directly to folder */

proc export 
  data=devsas.ATT_readmit_mostRecent_&MMYY 
  dbms=xlsx 
  outfile="C:\SAS\output_files\reAdmit_mostRecent&MMYY" 
  replace;
run;

proc export 
  data=devsas.ATT_readmit_longestDays_&MMYY 
  dbms=xlsx 
  outfile="C:\SAS\output_files\reAdmit_longestDays&MMYY" 
  replace;
run;

proc export 
  data=devsas.ATT_readmit_dateMatch_&MMYY
  dbms=xlsx 
  outfile="C:\SAS\output_files\reAdmit_dateMatch&MMYY" 
  replace;
run;

 


Accepted Solutions
Solution
‎09-12-2016 12:14 PM
Super Contributor
Posts: 376

Re: Macro for inputting and outputting different files

And what was the error message?  To quote Jerry Maguire "Help me help you".

Did you download %loop and compile in your current SAS session?

Did you download the %parmv supporting macro and compile it in your current SAS session?

 


devsas wrote:

Lets say I have written four different codes using macro variables and I want to write one single macro code which recalls each one of those codes.


 

That's what my example did, as long as you download the macros, and read and understand them.

 

One last example:

 

%macro code;
   %put &word;
%mend;
%loop(
C:\Temp\MyFile1.dat
C:\Temp\MyFile2.dat
C:\Temp\MyFile3.dat
C:\Temp\MyFile4.dat
)

View solution in original post


All Replies
Super Contributor
Posts: 376

Re: Macro for inputting and outputting different files

I don't know if this helps...your post was a bit of a code dump and a lot to take in.  I'm not entirely sure what you want to do.  If you could summarize a bit that would help.

 

However, see https://github.com/scottbass/SAS/tree/master/Macro.  Review the %loop and %loop_control macros (and supporting macros such as %parmv).

 

You could create a series of macros encapsulating what you need to do for each table, with the macro name matching your table names.

 

Using the %loop macro, it would go something like this:

 

 

%macro class;
   proc print data=sashelp.class;run;
%mend;
%macro shoes;
   proc print data=sashelp.shoes;run;
%mend;
%macro cars;
   proc print data=sashelp.cars;run;
%mend;
%macro code;
   %&word;
%mend;
%loop(shoes class cars)

HTH...

Frequent Contributor
Posts: 87

Re: Macro for inputting and outputting different files

Sorry Scott for confusing you! Ok, let me try to rephrase and make it simple for demonstration purpose. Lets say I have written four different codes using macro variables and I want to write one single macro code which recalls each one of those codes. So rather than me running each code seperately, I run one code by changing variable names (file names and date only) and rest all is taken care of. Lets say the first code uses input file A, second code uses input file B and so on. Macro should do If file =A then run code 1, if file =B, then run code B and so on. Hope this is clearer. I ran your code to test on my SAS, but it gave an error for loop. Thanks again!

Solution
‎09-12-2016 12:14 PM
Super Contributor
Posts: 376

Re: Macro for inputting and outputting different files

And what was the error message?  To quote Jerry Maguire "Help me help you".

Did you download %loop and compile in your current SAS session?

Did you download the %parmv supporting macro and compile it in your current SAS session?

 


devsas wrote:

Lets say I have written four different codes using macro variables and I want to write one single macro code which recalls each one of those codes.


 

That's what my example did, as long as you download the macros, and read and understand them.

 

One last example:

 

%macro code;
   %put &word;
%mend;
%loop(
C:\Temp\MyFile1.dat
C:\Temp\MyFile2.dat
C:\Temp\MyFile3.dat
C:\Temp\MyFile4.dat
)
Frequent Contributor
Posts: 87

Re: Macro for inputting and outputting different files

lol..you are a rockstar!

Thanks so much! It worked, and sorry for being casual in my approach (still learning Smiley Happy)

I just tested it, but have to spend some time learning what exactly it is doing. I will have more questions.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 300 views
  • 0 likes
  • 2 in conversation