topic Re: HELP NEEDED: Code to rearrange data in SAS Procedures

HELP NEEDED: Code to rearrange data

wriccar — Fri, 01 Mar 2019 14:21:37 GMT

Hello fellow SAS users,

Hoping someone can help me write a code for a relatively simple (but tedious) procedure. My data set includes dates (beginning and ending) during which an error occurred for a given observation. About 41% of Groups have errors in multiple periods. The data looks like this:

Group Name	Type	Error Type	Period Beg	Period End
AAABBB	1	F	1/1/2003	12/31/2003
AAABBB	1	R	4/1/2004	6/30/2004

I need each Group to correspond to only a single observation, so that line of data needs to capture all the different error periods. Something like this:

Group Name	Type	Error Type	Period Beg	Period End	Error Type2	Period Beg2	Period End2
AAABBB	1	F	1/1/2003	12/31/2003	R	4/1/2004	6/30/2004

Some groups have only a single error period, some have many. I was using a pretty rudimentary program using lags but it was not efficient. Any help would be greatly appreciated.

Re: HELP NEEDED: Code to rearrange data

VDD — Fri, 01 Mar 2019 14:32:00 GMT

@wriccar your sample data should included some samples that your request address.

When the sample size is to small the results will not always include possible constraints that need to be considered.

Re: HELP NEEDED: Code to rearrange data

RW9 — Fri, 01 Mar 2019 14:39:17 GMT

Why do you need the second example? The data as stored in the first example is a better method of storing the data.

The only time it might be useful is for a report, in which case three proc transposes, then merge back together by group name.

Re: HELP NEEDED: Code to rearrange data

wriccar — Fri, 01 Mar 2019 14:41:26 GMT

The end goal of what I'm asking is because I need to merge with a second dataset, and identify if the period from that dataset corresponds to an error period. If there are multiple observations for the same Group, then the merge does not take in all of those different error periods.

Re: HELP NEEDED: Code to rearrange data

RW9 — Fri, 01 Mar 2019 14:48:25 GMT

Please show concrete examples, presenting test data in the form of a datastep for each input dataset:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

I do not see how merging with one record rather than multiple records would be any different, its exactly the same data, if date fits into one of the time periods then it merges onto that row??

Re: HELP NEEDED: Code to rearrange data

wriccar — Fri, 01 Mar 2019 16:19:49 GMT

I solved my problem so no need for further replies. Thank you!

Re: HELP NEEDED: Code to rearrange data

VDD — Fri, 01 Mar 2019 16:21:37 GMT

Please post your solution so the community benefits also.