Finding where value in a column are repeated and putting them together

librasonali — Sat, 03 Jul 2021 06:47:23 GMT

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

Re: Finding where value in a column are repeated and putting them together

Patrick — Sat, 03 Jul 2021 07:20:16 GMT

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;

topic Finding where value in a column are repeated and putting them together in SAS Programming

Finding where value in a column are repeated and putting them together

Re: Finding where value in a column are repeated and putting them together