Creating new columns where data doesn't match

[mmagnuson]

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.

[PaigeMiller]

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-dat...

[jebjur · Accepted solution]

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;

[mmagnuson]

Thank you so much!!

[mmagnuson]

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