topic Macro variable and DATA step-Part 2 in SAS Procedures

Macro variable and DATA step-Part 2

ren2010 — Tue, 08 Jun 2010 17:27:46 GMT

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.

Re: Macro variable and DATA step-Part 2

Flip — Tue, 08 Jun 2010 19:00:24 GMT

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)

Re: Macro variable and DATA step-Part 2

Ksharp — Wed, 09 Jun 2010 07:46:24 GMT

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

Re: Macro variable and DATA step-Part 2

ren2010 — Wed, 09 Jun 2010 19:38:43 GMT

Thanks Ksharp and Flip

Re: Macro variable and DATA step-Part 2

ren2010 — Wed, 09 Jun 2010 19:48:52 GMT

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.

Re: Macro variable and DATA step-Part 2

Ksharp — Thu, 10 Jun 2010 07:08:22 GMT

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.

Re: Macro variable and DATA step-Part 2

ren2010 — Thu, 10 Jun 2010 16:01:54 GMT

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.

Re: Macro variable and DATA step-Part 2

Flip — Thu, 10 Jun 2010 17:12:01 GMT

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.

Re: Macro variable and DATA step-Part 2

Ksharp — Fri, 11 Jun 2010 08:38:46 GMT

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.......

Re: Macro variable and DATA step-Part 2

ren2010 — Fri, 11 Jun 2010 15:51:53 GMT

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.

Re: Macro variable and DATA step-Part 2

Flip — Fri, 11 Jun 2010 16:11:25 GMT

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

Re: Macro variable and DATA step-Part 2

Ksharp — Sat, 12 Jun 2010 07:18:13 GMT

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.

Re: Macro variable and DATA step-Part 2

ren2010 — Fri, 18 Jun 2010 22:05:50 GMT

Once Again Thank you to Ksharp and Flip.