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

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Oh . I would suggest to use data step , it was usually faster than sql in this scenario .

View solution in original post

10 REPLIES 10
stat_sas
Ammonite | Level 13

What are you trying to do based on this query?

Tom
Super User Tom
Super User

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'

;

pallis
Fluorite | Level 6

Sorry for late reply.

Here my intention was to create month on month designation of the employee

Tom
Super User Tom
Super User

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;

Ksharp
Super User

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

pallis
Fluorite | Level 6

,

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.

Ksharp
Super User

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  .

pallis
Fluorite | Level 6

HI All,

Sorry for the late reply.

   My data is 173424. When I apply the above logic the query is taking more than 30 min. I have 2000 records in my master. I created index. But still no use of it. Is there any other way to increase performance.

Can you suggest me.

Ksharp
Super User

Oh . I would suggest to use data step , it was usually faster than sql in this scenario .

pallis
Fluorite | Level 6

I Transpose the data and mapped with by transactional data. It worked issue is solved. Thanks for your help

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
  • 10 replies
  • 4432 views
  • 7 likes
  • 4 in conversation