topic Re: Help with rolling data into one row in New SAS User

Help with rolling data into one row

corji — Thu, 09 Jan 2020 23:03:14 GMT

Hi, I'm having a little difficulty with the data below:

datalines;
01OCT2017 XX-1 XX 0
01JAN2018 XX-1 XX 7320
01APR2018 XX-1 XX 30240
01JUL2018 XX-1 XX 40600
01OCT2018 XX-1 XX 45360
01JAN2019 XX-1 XX 29302
01APR2019 XX-1 XX 0
01OCT2017 XX-2 XX 0
01JAN2018 XX-2 XX 7320
01APR2018 XX-2 XX 30240

In the above data each individual, signified with the -(number) is part of the overall group XX. Each individual was given a certain amount of money, denoted by the final column; however, the numbers shown are incorrect, as they signify the total for the group, not the individual (as in, for period 1 Jan 2018, 7320 was issued for XX in total, not just XX-1).

I want to find a way to report out the money given out per time period for XX without the duplicating of values in the money column. Ideally it should look something like this:

Time Period Group Money Issued

01OCT2017 XX 0
01JAN2018 XX 7320
01APR2018 XX 30240
01JUL2018 XX 40600

and so on.

Could someone help with thinking this through?

Re: Help with rolling data into one row

PaigeMiller — Thu, 09 Jan 2020 23:14:46 GMT

I don't understand the problem. From your output, it seems like you just want to remove XX-1 or XX-2 from each record. Is the logic really that simple, or are there other steps? What happened to the other lines in the input that are not represented in the output?

Re: Help with rolling data into one row

corji — Thu, 09 Jan 2020 23:28:13 GMT

It's a bit more complicated than that (at least in my head). I basically want to keep only one individual (say XX-1) for all time periods, using that as the basis for XX.

But there are multiple groups too, so I'd want to keep 1 of XX, one of AA, etc. Does that make sense?

Re: Help with rolling data into one row

unison — Fri, 10 Jan 2020 04:06:27 GMT

I think you're looking for the unique entries by time period. nodupkey as it's used below will give you the desired output.

data have;
	input time_period :date9. member $ group $ money_issued;
	format time_period date9.;
	datalines;
01OCT2017 XX-1 XX 0
01JAN2018 XX-1 XX 7320
01APR2018 XX-1 XX 30240
01JUL2018 XX-1 XX 40600
01OCT2018 XX-1 XX 45360
01JAN2019 XX-1 XX 29302
01APR2019 XX-1 XX 0
01OCT2017 XX-2 XX 0
01JAN2018 XX-2 XX 7320
01APR2018 XX-2 XX 30240
;
run;

data desired_output;
	input time_period :date9. group $ money_issued;
	format time_period date9.;
	datalines;
01OCT2017 XX 0
01JAN2018 XX 7320
01APR2018 XX 30240
01JUL2018 XX 40600
01OCT2018 XX 45360
01JAN2019 XX 29302
01APR2019 XX 0
;
run;

proc sort data=have(drop=member) nodupkey out=want;
	by time_period;
run;

proc compare base=desired_output compare=want;
run;

Re: Help with rolling data into one row

corji — Fri, 10 Jan 2020 17:21:06 GMT

I think the issue with this solution is that it de-dupes the time periods as well, which I want to keep as is: the periods need to remain as there are other unique groups (think YY to XX, ZZ etc.) that need to have that time period as an identifier. XX isn't the only group.

Re: Help with rolling data into one row

unison — Fri, 10 Jan 2020 19:21:01 GMT

If I understand what you're saying, try:

proc sort data=have(drop=member) nodupkey out=want;
	by time_period group;
run;

Re: Help with rolling data into one row

corji — Mon, 13 Jan 2020 19:19:54 GMT

This was the one that worked! Thank you.