Hello Community, just joined your Community a few hours ago. Hope you are having a grand day! I started learning SAS/SQL not even a month ago. I moved on to macros and I cannot seem to puzzle out a programming bit that involves checking missing months and inserting them into the table. Here's what my data looks like. The two main columns are SEGM_DATE and CLIENT_ID along with numerous other various columns, such as AVG_WEIGHT that consists primarily of numeric values. SEGM_DATE CLIENT_ID AVG_WEIGHT 01MAR2012:00:00:00 1-ET-1500 8 01APR2012:00:00:00 1-ET-1500 10 01JUN2012:00:00:00 1-ET-1500 13 What I need to achieve is to loop over the table and insert any missing months into the table, filling all other_various_columns, such as AVG_WEIGHT with NULL values. For example: MAR 1-ET-1500 8 APR 1-ET-1500 10 MAY 1-ET-1500 NULL JUN 1-ET-1500 13 The second part, which I firmly believe I figured out, is to create two additional columns, one that is named AVG_WEIGHT_month_ago and carries value from previous month and AVG_WEIGHT_2mon_ago and carries value from two months ago, e.g.: SEGM_DATE CLIENT_ID AVG_WEIGHT AVG_WEIGHT_month_ago AVG_WEIGHT_2mon_ago MAR 1-ET-1500 8 NULL NULL APR 1-ET-1500 10 8 NULL MAY 1-ET-1500 NULL 8 10 JUN 1-ET-1500 13 NULL 10 Here is what my code looks like so far. proc import datafile="D:/somefile.csv" out=csv out=challenge replace;
delimiter='09'x;
run;
%macro challenge_macro(lib=, table=);
proc sql;
select name into: vars separated by ' ' from dictionary.columns
where libname="&lib" and memname="&table";
quit;
%put &vars;
options validvarname=v7;
data challengesolution (drop=SEGM_DATE CLIENT_ID);
set &lib..&table;
%local i column_name column_var;
%do i=1 %to %sysfunc(countw(&vars));
%let column_var = %scan(&vars, &i);
%let column_name = %substr(&column_var, 1, 10);
%put &column_var &column_name;
&column_name._last_m = lag(&column_name);
&column_name._last_two_m = lag2(&column_name);
if first.&column_name then
&column_name._last_m=&column_name;
if first.&column_name then
&column_name._last_two_m=&column_name;
%end;
%mend;
%challenge_macro(lib=WORK, table=CHALLENGE); Please, would be so kind and help me with identifying missing months and insert any missing months into the table? I did try numerous approaches, though not a single one panned out to be right.
... View more