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

Hello,

 

I have a table which has 3 character columns and then another 5 character columns.

 

I want to collapse the table so there is only 1 row when first 3 columns are same.

 

For the remaining 5 columns I want to take the value from the only record in the group which will be non-blank.

 

eg.

 

A B C  xxx ___  ___ ___ ___

A B C  ___ yyy  ___ ___ ___

A B C  ___ ___  ___ ___ ttt

A B C  ___ ___  zzz ___ ___

A B C  ___ ___  ___ sss  ___

X Y Z  ___ bbb ___ ___ ___

X Y Z  ___ ___ ___ ___ aaa

 

Desired result:

A B C xxx yyy zzz sss ttt

X Y Z ___ bbb ___ ___ aaa

 

Any assistance will be liked promptly.

 

Kind regards,

Mark

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Use by-group processing (by your first three columns) in a data step.

retain new variables for each column you want to accumulate.

at first.(third variable of the by), set all your new variables to missing/empty

everytime you encounter a non-missing value, assign it to the corresponding new variable

at last.(third variable of the by), output.

keep the three by-variables an the new variables

View solution in original post

6 REPLIES 6
ChrisNZ
Tourmaline | Level 20

To get prompt assistance, provide a detailed example. Your request is very vague at best.

 

Edit: Much better now.  I now see why @Reeza quotes the question in the first reply 🙂

Kurt_Bremser
Super User

Use by-group processing (by your first three columns) in a data step.

retain new variables for each column you want to accumulate.

at first.(third variable of the by), set all your new variables to missing/empty

everytime you encounter a non-missing value, assign it to the corresponding new variable

at last.(third variable of the by), output.

keep the three by-variables an the new variables

s_lassen
Meteorite | Level 14

One solution is to use merge with WHERE clauses, assuming that your variables are named var1 through var8:

data want;

  merge

    have(keep=var1 var2 var3 var4 where=(var4))

    have(keep=var1 var2 var3 var5 where=(var5)) 

    have(keep=var1 var2 var3 var6 where=(var6))

    have(keep=var1 var2 var3 var7 where=(var7))

    have(keep=var1 var2 var3 var8 where=(var8))

    ;

  by var1 var2 var3;

run;


ChrisNZ
Tourmaline | Level 20

To update records while ignore missing values, the UPDATE statement is usually used.

Ksharp
Super User

data want;

 update have(obs=0) have;

 by id;

run;

Astounding
PROC Star

@ChrisNZ and @Ksharp are moving in the right direction.  SInce you haven't told us what the names of your variables are, I'll just use COL1, COL2, and COL3 (and don't care about the other names).  This should do it:

 

proc sort data=have;

by col1 col2 col3;

run;

data want;

update have (obs=0) have;

by col1 col2 col3;

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
  • 6 replies
  • 1426 views
  • 5 likes
  • 6 in conversation