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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: