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.
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;
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...
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;
Thank you so much!!
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: