topic Re: Creating new columns where data doesn't match in SAS Enterprise Guide

Creating new columns where data doesn't match

mmagnuson — Tue, 30 Jul 2019 16:58:24 GMT

Hi, I'm trying to create new columns so that my data can fit into a single row instead of having multiple rows. I need this data to show this way because I'm trying to see what states at what times using the ID field. I'm unsure how to go about doing this and have shared an excel document that might make more sense.

Re: Creating new columns where data doesn't match

PaigeMiller — Tue, 30 Jul 2019 17:08:16 GMT

Many of us will not (or can not) download Excel (or Word or other MS Office) documents because they are a security threat.

Please provide your data as described in these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Creating new columns where data doesn't match

jebjur — Tue, 30 Jul 2019 20:33:32 GMT

You will need to use arrays do get the results you want. You will need an array for every variable you want to collapse, and the number of variables in each array will depend on how many observations you have for each BY group (ID). For example:

/* sample data */

data test;
input ID $ State $ Start :date9. Stop :date9. Location $;
format start date9. stop date9.;
cards;
10101 MA 01Jan2019 30MAR2019 Sandwich
10101 CT 03APR2019 31MAY2019 Bolton
20202 NC 05MAY2019 30JUN2019 Raleigh
20202 FL 02JUL2019 30AUG2019 Miami
;
run;

data new (keep=ID state1-state2 start1-start2 stop1-stop2 loc1-loc2);
retain ID state1-state2 start1-start2 stop1-stop2 loc1-loc2;
array x (2) $ state1-state2;
array y (2) start1-start2;
array z (2) stop1-stop2;
array l (2) $ loc1-loc2;

set test;
by ID;

if first.ID then
do;
i=1;

do j=1 to 2;
x(j)=' ';
y(j)=.;
z(j)=.;
l(j)=' ';
end;
end;

x(i)=state;
y(i)=start;
z(i)=stop;
l(i)=location;

format start1-start2 date9. stop1-stop2 date9.;

if last.id then output;
i+1;
run;

Re: Creating new columns where data doesn't match

mmagnuson — Wed, 31 Jul 2019 12:31:22 GMT

Sorry for the attachment. Here is the data and what I want it to look like

Data
ID	State	Start	Stop	Location
10101	MA	1-Jan-19	30-Mar-19	Sandwhich
10101	CT	1-Apr-19	May312019	Bolton

What I want it to look like
ID	State 1	Location 1	Start 1	Stop 1	State 2	Location 2	Start 2	Stop 2
10101	MA	Sandwhich	1-Jan-19	30-Mar-19	CT	Bolton	1-Apr-19	May312019

Re: Creating new columns where data doesn't match

mmagnuson — Wed, 31 Jul 2019 15:20:29 GMT

Thank you so much!!