hi ,
PFB my SAS code where i want DaysCount which i have completed but could not figure out how i can generate ConsolidatedMonths ?
ConsolidatedMonths -- Where the "days" field corresponding months should be populated.
Thanks in advance 🙂
data calendar; input Month $ Dates mmddyy8.; cards; MAR 03/04/18 MAR 03/13/18 MAR 03/18/18 MAR 03/25/18 APR 04/01/18 APR 04/08/18 APR 04/15/18 APR 04/22/18 APR 04/25/18 MAY 05/06/18 MAY 05/13/18 MAY 05/20/18 MAY 05/27/18 ; proc print; run; data out; set calendar; Days=day(Dates); run; proc sort data=out; by Days; run; Data out1; set out; by Days; retain DaysCount; if first.Days then DaysCount = 1 ; else DaysCount = DaysCount + 1; if last.Days then output; proc print; run;
My current output is :
Obs | Month | Dates | Days | DaysCount |
1 | APR | 21275 | 1 | 1 |
2 | MAR | 21247 | 4 | 1 |
3 | MAY | 21310 | 6 | 1 |
4 | APR | 21282 | 8 | 1 |
5 | MAY | 21317 | 13 | 2 |
6 | APR | 21289 | 15 | 1 |
7 | MAR | 21261 | 18 | 1 |
8 | MAY | 21324 | 20 | 1 |
9 | APR | 21296 | 22 | 1 |
10 | APR | 21299 | 25 | 2 |
11 | MAY | 21331 | 27 | 1 |
Expected Output :
Obs | Month | Dates | Days | DaysCount | ConsolidatedMonths |
1 | APR | 21275 | 1 | 1 | APR |
2 | MAR | 21247 | 4 | 1 | MAR |
3 | MAY | 21310 | 6 | 1 | MAY |
4 | APR | 21282 | 8 | 1 | APR |
5 | MAY | 21317 | 13 | 2 | MAR,MAY |
6 | APR | 21289 | 15 | 1 | APR |
7 | MAR | 21261 | 18 | 1 | MAR |
8 | MAY | 21324 | 20 | 1 | MAY |
9 | APR | 21296 | 22 | 1 | APR |
10 | APR | 21299 | 25 | 2 | MAR, APR |
11 | MAY | 21331 | 27 | 1 | MAY |
Below code creates your expected result.
data calendar;
input Month $ Dates mmddyy8.;
format dates date9.;
cards;
MAR 03/04/18
MAR 03/13/18
MAR 03/18/18
MAR 03/25/18
APR 04/01/18
APR 04/08/18
APR 04/15/18
APR 04/22/18
APR 04/25/18
MAY 05/06/18
MAY 05/13/18
MAY 05/20/18
MAY 05/27/18
;
data out;
set calendar;
Days=day(Dates);
_month_num=month(Dates);
run;
proc sort data=out;
by Days _month_num;
run;
Data out1(drop=_:);
set out;
by Days _month_num;
length DaysCount 8 ConsolidatedMonths $20;
retain ConsolidatedMonths;
if first._month_num then
do;
DaysCount+1;
ConsolidatedMonths=catx(',',ConsolidatedMonths,put(dates,monname3.));
end;
if last.Days then
do;
output;
call missing(DaysCount, ConsolidatedMonths);
end;
run;
proc print;
run;
Below code creates your expected result.
data calendar;
input Month $ Dates mmddyy8.;
format dates date9.;
cards;
MAR 03/04/18
MAR 03/13/18
MAR 03/18/18
MAR 03/25/18
APR 04/01/18
APR 04/08/18
APR 04/15/18
APR 04/22/18
APR 04/25/18
MAY 05/06/18
MAY 05/13/18
MAY 05/20/18
MAY 05/27/18
;
data out;
set calendar;
Days=day(Dates);
_month_num=month(Dates);
run;
proc sort data=out;
by Days _month_num;
run;
Data out1(drop=_:);
set out;
by Days _month_num;
length DaysCount 8 ConsolidatedMonths $20;
retain ConsolidatedMonths;
if first._month_num then
do;
DaysCount+1;
ConsolidatedMonths=catx(',',ConsolidatedMonths,put(dates,monname3.));
end;
if last.Days then
do;
output;
call missing(DaysCount, ConsolidatedMonths);
end;
run;
proc print;
run;
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.