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

Hi Professionals,

 

I created a simple sample for my tables.

the original one is like:

APPLICATIONCODE1CODE2CODEB1CODEB2
1111FSFS
1112N N 
1113CF CF 
1114  B<40 
1115  FS
1116  F 

my goal is to create:

APPLICATIONCODE1CODE2
1111FS
1112N 
1113CF 
1114B<40 
1115FS
1116F 

For each application, when code1 ='' and code2=''  let code1=codeb1 and code2=codeb2. for the rest code 1 or code2, just keep their original value.

then delete codeb1 and codeb2.

 

How can I do the merge?

data new;

set origianl;

if code1='' and code2='' then code1=codeb1 and code2=codeb2;

else (here I dont' know how to describe let codes = original value);

run;

 

thank you for anyone who helps.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

You are actually most of the way there. What you are missing in concept is if the values are not both missing then you are actually saying "do nothing, leave it alone". However you can only have one assignment per statement so you have to assign the values of code1 and code2 separately. To make them respond to the same condition then use a DO; End; block to group them.

You want to drop the extra variables or alternatively use KEEP to keep the ones you want.

So

data new;

set origianl;

if code1='' and code2='' then Do;

   code1=codeb1;

   code2=codeb2;

end;

drop codeb1 codeb2;

/* or Keep application code1 code2;*/

run;

View solution in original post

7 REPLIES 7
ballardw
Super User

You are actually most of the way there. What you are missing in concept is if the values are not both missing then you are actually saying "do nothing, leave it alone". However you can only have one assignment per statement so you have to assign the values of code1 and code2 separately. To make them respond to the same condition then use a DO; End; block to group them.

You want to drop the extra variables or alternatively use KEEP to keep the ones you want.

So

data new;

set origianl;

if code1='' and code2='' then Do;

   code1=codeb1;

   code2=codeb2;

end;

drop codeb1 codeb2;

/* or Keep application code1 code2;*/

run;

collinelliot
Barite | Level 11

I think my solution works with the data, but not with the description. The above addresses the problem described better, I think.

septemberbulb
Obsidian | Level 7
THANK YOU VERY MUCH.
collinelliot
Barite | Level 11

 

I'd just use the coalesce function to get what you want:

 

data have;
infile datalines dsd;
input APPLICATION CODE1 $ CODE2 $ CODEB1 $ CODEB2 $;
datalines;
1111, F, S, F, S
1112, N,  , N,  
1113, CF,  , CF,  
1114,  ,  , B<40,  
1115,  ,  , F, S
1116,  ,  , F,  
;


data want;
    set have;
    code1 = coalescec(code1, codeb1);
    code2 = coalescec(code2, codeb2);
run;
anoopmohandas7
Quartz | Level 8

Use a CASE statement as show in below sample code.

 

proc sql;
select 
case 
when CODE1 = ' ' then CODEB1 else CODE1 end as CODE1
from test ;
quit ;

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
  • 7 replies
  • 1014 views
  • 2 likes
  • 4 in conversation