data a1;
input sno name $ sal;
datalines;
1 jagan 300
2 Karti 400
3 Vankat 500
2 jagan 300
2 Karti 400
3 Vankat 500
;
run;
data a2;
input month$ sno role$;
datalines;
jan 1 Clerk
Feb 2 Cook
Nar 3 Account
;
run;
Looking for output:
Sno name sal jan feb
1 jagan 300 clerk cook
2 Karti 400 cook cook
3 venkat 500 accounts Accounts
Note: For every new month I must get a new column(one month lag).
Ex If I'm in month of april I must fill there roles for month march and column name as march.
Below is the query which i wrote but i'm getting error
proc sql;
create table a3
as
select a.*,
case when sno in (select sno from a2 where month='jan')
then
( select b.role from a1 as a left join a2 as b
on a.sno=b.sno)
else '#n/a' end as Jan
from a1 as a;
quit;
Please help out to solve this issue
Oh . I would suggest to use data step , it was usually faster than sql in this scenario .
What are you trying to do based on this query?
Looks to me like you are just doing a left join of the two tables.
create table a3 as
select a1.*
, coalesce(a2.role,'#n/a') as Jan
from a1 left join a2
on a1.sno=a2.sno
and a2.month = 'jan'
;
Sorry for late reply.
Here my intention was to create month on month designation of the employee
Use PROC TRANSPOSE to convert A2.
proc transpose data=a2 out=month_role ;
by sno;
id month;
var role ;
run;
Then just merge by SNO.
data want ;
merge a1 month_role ;
by sno;
run;
Since you didn't post what output you need . Try this one :
data a1; input sno name $ sal; datalines; 1 jagan 300 2 Karti 400 3 Vankat 500 2 jagan 300 2 Karti 400 3 Vankat 500 ; run; data a2; input month$ sno role$; datalines; jan 1 Clerk Feb 2 Cook Nar 3 Account ; run; proc sql; create table a3 as select a.*, case when (select count(*) from a2 where month='jan' and sno=a.sno) gt 0 then ( select role from a2 where sno=a.sno) else '#n/a' end as Jan from a1 as a; quit;
Xia Keshan
Can you explain me the process/execution steps how this case statements works
case when (select count(*) from a2 where month='jan' and sno=a.sno) gt 0
then
( select role from a2 where sno=a.sno)
from a1 as a.
In the Then Clause if there are multiple records then clause Will return more than one row isnt. Can you correct me if im wrong.
when there are multiple obs selected in A2 with month='jan' and sno equal the current obs's sno
(select count(*) from a2 where month='jan' and sno=a.sno) gt 0
then set it as role of A2 when sno equal the current obs's sno .
Oh . I would suggest to use data step , it was usually faster than sql in this scenario .
I Transpose the data and mapped with by transactional data. It worked issue is solved. Thanks for your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.