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

Hello,

 

Say that I have dataset1 with ID and variable1, like this:

IDVar1
A1
B2
C3
D4
E5

 

I also have dataset2, which contains a reference variable, like this:

RefVar
1
2
3

 

How do I modify dataset1 such that any Var1's values not found in dataset2's Refvar will be converted to missing? The resulting dataset1 should look like this:

IDVar1
A1
B2
C3
D.
E.

 

The actual dataset has a lot of values for "Var1" and "RefVar" so a PROC SQL solution would be ideal, I believe.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data one;
input ID $	Var1;
datalines;
A	1
B	2
C	3
D	4
E	5
;

data ref;
input RefVar;
datalines;
1
2
3
;

proc sql;
create table want as
select id ,var1+refvar-refvar as var1
from one left join ref on one.var1=ref.refvar
order by id,var1;
quit;

View solution in original post

5 REPLIES 5
Reeza
Super User

I would use dataset2 to create a format and then apply the format as desired. 

 

You can use the hlo='O' to create the other record to assign it to missing if desired.

 

 

novinosrin
Tourmaline | Level 20
data one;
input ID $	Var1;
datalines;
A	1
B	2
C	3
D	4
E	5
;

data ref;
input RefVar;
datalines;
1
2
3
;

proc sql;
create table want as
select id ,var1+refvar-refvar as var1
from one left join ref on one.var1=ref.refvar
order by id,var1;
quit;
torvyle
Calcite | Level 5

Thank you very much.

 

If Var1 and Refvar variables happen to be string would creating a format using the reference dataset, like what Reeza said, be the only option or is there a PROC SQL code for that? Would I have to create a new thread for this?

novinosrin
Tourmaline | Level 20

in that case, take the refvar as var 🙂

data one;
input ID $	Var1 $;
datalines;
A	a
B	b
C	c
D	d
E	e
;

data ref;
input RefVar $;
datalines;
a
b
c
;


proc sql;
create table want as
select id ,refvar as var1
from one left join ref on one.var1=ref.refvar
order by id,2;
quit;

This works for both string and numerics as you can tell from the code by replacing the var1 with ref from table2 

torvyle
Calcite | Level 5

Works as well. Thank you!

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
  • 5 replies
  • 780 views
  • 0 likes
  • 3 in conversation