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

Hey guys,

Ya'll have been so helpful! Here's another question I have. I have a data set with seven columns (variables). However, two of these columns represent the same variable, but they have different values. For instance:

Var1Var2Var3Var4aVar4bVar5Var6

1

ay15152323orange$500
2bn26263434blue$600
3cy37374545green$700
4dn48485656red$800

Var4a and Var4b are the columns in question. All column attributes above correspond to the values in Var4a (ie, the values of 1, a, y, orange, and $500 correspond to 1515 in Var4a).

But I guess that's not too terribly important. What I'd like to do is take Var4a and stack it on top of Var4b, and get something like this:

Var1Var2Var3Var4Var5Var6
1ay1515orange$500
2bn2626blue$600
3cy3737green$700
4dn4848red$800
...2323..
...3434..
...4545..
...5656..

Is there any simple way to do this, or is it some long convoluted mess of transposes and left joins and sorcery?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Keith
Obsidian | Level 7

Here's a simple way.

data have;

input var1 var2 $ var3 $ var4a var4b var5 $ var6;

cards;

1 a y 1515 2323 orange 500

2 b n 2626 3434 blue 600

3 c y 3737 4545 green 700

4 d n 4848 5656 red 800

;

run;

data want;

set have (drop=var4b rename=(var4a=var4))

  have (keep=var4b rename=(var4b=var4));

run;

View solution in original post

3 REPLIES 3
Keith
Obsidian | Level 7

Here's a simple way.

data have;

input var1 var2 $ var3 $ var4a var4b var5 $ var6;

cards;

1 a y 1515 2323 orange 500

2 b n 2626 3434 blue 600

3 c y 3737 4545 green 700

4 d n 4848 5656 red 800

;

run;

data want;

set have (drop=var4b rename=(var4a=var4))

  have (keep=var4b rename=(var4b=var4));

run;

jtrousd
Calcite | Level 5

You're awesome, Keith! Thanks!

Haikuo
Onyx | Level 15

If you have some sort of group id to be considered, then the following code may help a little:

DATA have;

input (Var1    Var2    Var3    Var4a    Var4b    Var5    Var6) ($);

group=floor(_n_/4);

cards;

1    a    y    1515    2323    orange    $500

2    b    n    2626    3434    blue    $600

3    c    y    3737    4545    green    $700

4    d    n    4848    5656    red        $800

1    a    y    1515    2323    orange    $500

2    b    n    2626    3434    blue    $600

3    c    y    3737    4545    green    $700

4    d    n    4848    5656    red        $800

;

data want;

array vb (0:999) $  _temporary_;

set have;

by group;

if first.group then call missing(i);

i+1;

vb(i)=var4b;

output;

if last.group then do _n_=1 to i;

call missing (of _all_);

   var4a=vb(_n_);

   output;

end;

drop var4b i;

run;

 

proc print;RUN;

Regards,

Haikuo

Update: On my second thought, Keith's solution is superior even there is a group id:

data want;

set have (drop=var4b rename=(var4a=var4))

  have (keep=group var4b rename=(var4b=var4));

  by group;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 822 views
  • 3 likes
  • 3 in conversation