topic MACRO in SAS Programming

MACRO

Thalitacosta — Fri, 18 Mar 2022 21:53:25 GMT

I have a "MOD" period that goes from OCt/2019 to Dec/2019 and another "OOT" period that goes from Jan/2020 to MAR/2020.

How do I call these values only once? Because in the code I have, I need to declare it in "LET" and at the end of the macro another six times.

%let dt1_mod_start = 201910;

%let dt2_mod_end = 201912;

%let dt1_oot_start = 202001;

%let dt2_oot_end= 202003;

%MACRO PSI (dt);

data dtbase1;

set dtbase;

if &dt1_mod_start. <= put(dta,yymmn6.) <= &dt2_mod_end. then period = "MOD";

else if &dt1_oot_start. <= put(dta,yymmn6.) <= &dt2_oot_end. then period = "OOT";

else period = "OTHER";

run;

proc sql;

create table perf_&dt. as select

dta format yymmn6. as date,

period,

leaf,

in_default,

count(*) as total

from dtbase1

where put(dta,yymmn6.) in ("&dt.")

group by date,period,leaf,in_defaul;

quit;

%mend;

%PSI (201910);

%PSI (201911);

%PSI (201912);

%PSI (202001);

%PSI (202002);

%PSI (202003);

Re: MACRO

Tom — Sat, 19 Mar 2022 03:10:26 GMT

What types of values does DTA contain? You are treating it as if it has date values, so the question is could the date value be for any day in the month? Or just the first day of the month? If it could be for different days in the same month then I don't think your SQL query will work right.

Re: MACRO

PaigeMiller — Sat, 19 Mar 2022 11:07:36 GMT

Show us a portion of data set DTBASE. Provide SAS data step code that will replicate DTBASE, or better yet follow these instructions. Do NOT provide data in other formats. Excel and screen captures is unacceptable.

Showing us a portion of the data as SAS data step code needs to be standard, something you do EVERY single time you ask questions. You have been in the forums a long time now, we should not have to ask you to show us the data in the proper format. We're trying to help you but you also have to help us.

Re: MACRO

Thalitacosta — Sat, 19 Mar 2022 13:19:24 GMT

DTA values are working, no different days.

Re: MACRO

Tom — Sat, 19 Mar 2022 16:52:33 GMT

So if the values of DTA are always the first day of the month then this should work.

First convert your YYYYMM strings into actual date values.

Then just use those date ranges in your SQL code.

No macro needed.

%let dt1_mod_start = 201910;
%let dt2_mod_end = 201912;
%let dt1_oot_start = 202001;
%let dt2_oot_end= 202003;

%let mod_start=%sysfunc(inputn(&dt1_mod_start,yymmn6.)):
%let mod_end=%sysfunc(inputn(&dt1_mod_end,yymmn6.)):
%let oot_start=%sysfunc(inputn(&dt1_oot_start,yymmn6.)):
%let oot_end=%sysfunc(inputn(&dt1_oot_end,yymmn6.)):

proc sql;
create table perf as 
  select
        dta format=yymmn6. as date
      , case when (&mod_start <= DTA <= &mod_end) then 'MOD'
             when (&oot_start <= DTA <= &oot_end) then 'OOT'
             else 'OTHER'
        end as period
      , leaf
      , in_default
      , count(*) as total
  from dtbase
  where min(&mod_start,&oot_start) <= DTA <= max(&mod_end,&oot_end)
  group by date,period,leaf,in_default
;
quit;