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;
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
)
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...
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!
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
)
lol..you are a rockstar!
Thanks so much! It worked, and sorry for being casual in my approach (still learning :))
I just tested it, but have to spend some time learning what exactly it is doing. I will have more questions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.