BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmagnuson
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jebjur
SAS Employee

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;

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
jebjur
SAS Employee

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
Quartz | Level 8

Thank you so much!!

mmagnuson
Quartz | Level 8

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

 

Data
IDStateStartStopLocation
10101MA1-Jan-1930-Mar-19Sandwhich
10101CT1-Apr-19May312019

Bolton

 

What I want it to look like
IDState 1Location 1Start 1Stop 1State 2Location 2Start 2Stop 2
10101MASandwhich1-Jan-1930-Mar-19CTBolton1-Apr-19May312019

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 953 views
  • 0 likes
  • 3 in conversation