BookmarkSubscribeRSS Feed
ren2010
Obsidian | Level 7
Hi All,
Thanks to all for the valuable comments.This thread is more about the actual problem I am facing.I am giving as example below.

file1
------
id sal dept
--- --- ------
1 200 sales
2 300 purchase
3 500 marketing

lookup file
----------------
id sal
-- -----
1 200
1 300
3 400


I read file1 and need to use the lookup table and if id and sal is in lookup file then set id and sal to spaces in file1.Once id and sal are set to spaces I need to use logic to assign proper values to id and sal based on dept.
once id and sal are spaces then logic to set id and sal are
---------------------------------------------------------------------------------
if dept = sales then id=s sal=250
id dep = purchase then id=p sal=350

if you can help me to fix this ,that would be really helpful

Thanks all in advance.
12 REPLIES 12
Flip
Fluorite | Level 6
I would think SQL would handle this.

Proc sql;
create table file3 as select
case when dept = 'sales' and l.sal ne . then 's'
when dept = 'purchase' and l.sal ne . then 'p'
else a.id end as id,
case when dept = 'sales' and l.sal ne . then 250
when dept = 'purchase' and l.sal ne . then 350
else a.sal end as sal,
dept from file1 a left join lookup l on a.id = l.id and a.sal = l.sal;

(untested)
Ksharp
Super User
I think you can directly overlap id and sal's values.
For set id =p s , here id is character informat.
and id,sal variates have been ordered by proc sort previously.

[pre]
data file1;
input id $ sal dept $;
datalines;
1 200 sales
2 300 purchase
3 500 marketing
;
data lookup;
input id $ sal;
datalines;
1 200
1 300
3 400
;
run;
data temp;
merge file1(in = in_file1) lookup(in = in_lookup);
by id sal;
if in_file1 and in_lookup then do;
if dept='sales' then do;
id='s'; sal=250;
end;
else if dept='purchase' then do;
id='p'; sal=350;
end;
end;
if in_file1;
run;
proc print noobs;
run;
[/pre]


Ksharp
ren2010
Obsidian | Level 7
Thanks Ksharp and Flip
ren2010
Obsidian | Level 7
Now I have a different situation:

data as;
input id sal cd$ dept$ ;
datalines;
1 200 aa aa
2 300 bb ss
;
run;

data lookup;
input id sal cd dept$;
datalines;
1 200 3 aa
;
run;

proc sql;
update as
set dept=' ' where id in (select id from lookup) and sal in (select sal from lookup) and
cd in (select cd from lookup) and dept in (select dept from lookup);
quit;

In the next step i have to populate all DEPT who has missing values with the values of CD.

In the above example after proc sql update the first record of file 'as' will look like:
id sal cd$ dept$
1 200 aa

and if I populate the missing dept with the value of aa again i make that row same as that of original ie 1 200 aa aa.

Is there any way I can check whether if the value of cd is in lookup when I fix all DEPT with null values and if its present in lookup I populate with NODEPT value.

Please let me know.
Thanks in advance.
Ksharp
Super User
Hi. I don't think your sql will work because sal =3 in lookup ,however sal =aa in as.
and i do not understand your mean totally.
If you want output like ' 1 200 aa aa ' , you can use filp ' s suggestion such as 'case when...' .

also can use ' lookup.id ' in sql to check whether if the value of cd is in lookup......
of course , data step can do it too.
ren2010
Obsidian | Level 7
Thansk Ksharp,I corrected the data ,and also mentioned my desired output below:


data as;
input id sal cd$ dept$ ;
datalines;
1 200 aa aa
1 200 aa bb
1 200 aa ee
2 300 bb ss
;
run;

data lookup;
input id sal cd$ dept$;
datalines;
1 200 aa aa
1 200 aa ee
;
run;

proc sql;
update as
set dept=' ' where id in (select id from lookup) and sal in (select sal from lookup) and
cd in (select cd from lookup) and dept in (select dept from lookup);
quit;

In the next step i have to populate all DEPT who has missing values with the values of CD.

In the above example after proc sql update the first record of file 'as' will look like:
id sal cd$ dept$
1 200 aa

and if I populate the missing dept with the value of aa again i make that row same as that of original ie 1 200 aa aa.

Is there any way I can check whether if the value of cd is in lookup when I fix all DEPT with null values and if its present in lookup I populate with NODEPT value.

My desired output is like this:

id sal cd$ dept$ ;
1 200 aa NODEPT
1 200 aa bb
1 200 aa NODEPT
2 300 bb ss


Thanks in advance.
Flip
Fluorite | Level 6
First back up and look at this line.
"set dept=' ' where id in (select id from lookup) and sal in (select sal from lookup) "

This looks to see if the ID is in the lookup, and then looks to see if the sal is in the lookup. It DOES NOT look to see if the ID and SAL are associated.
SO id = 1, SAL = 300 Will match if 2 records exist such as ID = 1, SAL = 200 and ID = 3, SAL = 300

Just add a case block for each condition and assign, there is no need to go through the data more than once.
Ksharp
Super User
Hi. Flip is right. Your sql have some problem.
try Flip's suggestion : " case when lookup.id eq as.id and lookup.sal eq as.sal ..... then ' ' " to check whether if the value of cd is in lookup when I fix all DEPT with null values.......
ren2010
Obsidian | Level 7
Thanks Flip and Khsharp,

The sql mentioned in my email is working fine.I did not see any issues when i ran it.It looks for the associated ID and SAL,not just an entry because I am using AND if had used OR then it would have picked any entry in the lookup table.

Once I set the dept =' ' then in the next data step I have to fix the dept values like this:
if dept=cd; and this time I wanted to check if the value of cd is in lookup table if the value of the cd is in lookup table then i make the dept=NODEPT.

Thanks once again for your suggestions.
Flip
Fluorite | Level 6
Well, you can't say we didn't warn you.
It may work in your simple data, but the 2 exist clauses are not associated no matter if you use OR or AND. Each exist looks to see if a record is in that table based on the one condition you have provided. proc sql;
update as a
set dept='NO DEPT' where exists (select id from lookup l where a.id = l.id and a.sal = l.sal and a.cd = l.cd and a.dept = l.dept) ;
quit;


Message was edited by: Flip
Ksharp
Super User
Hi. Your sql statement should not be error during compile stage.
But the output should not be what you want as your said.
Flip has some right suggestion.
ren2010
Obsidian | Level 7
Once Again Thank you to Ksharp and Flip.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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