topic Re: Cannot create SET , BY in SAS DI in SAS Data Management

Cannot create SET , BY in SAS DI

ven — Fri, 01 May 2015 00:37:52 GMT

I need to implement below in SAS DI without using custom code, Can we achieve it by using only the existing SAS DI transformations?

proc sort DATA=AIN out=A;

By AID AMEM;

data pt_all_LAC_curr (keep= AID AMEM All_AMEMs) ;
set A ;
by AID AMEM;

length All_AMEMs $255;

if first.AID then do;
retain All_AMEMs;
All_AMEMs=cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")");
end;

else do All_AMEMs=catx(" NEXT VAL ",cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")"));

end;
if last.AID then output;

run;

In SAS DI 4.21 or 4.8

For example:

INPUT

AID AMEM Plan_mgmt_team_member Participant_Management_Role_Desc

1 5 AA AA_DESC

1 5 BB BB_DESC

1 5 CC CC_DESC

2 6 DD DD_DESC

2 6 EE EE_DESC

2 6 FF FF_DESC

Expected OUTPUT:

AID AMEM All_AMEMs

1 5 AA (AA_DESC) NEXT VAL BB (BB_DESC) NEXT VAL CC (CC_DESC)

2 6 DD (DD_DESC) NEXT VAL EE (EE_DESC) NEXT VAL FF (FF_DESC)

Re: Cannot create SET , BY in SAS DI

Patrick — Fri, 01 May 2015 08:52:58 GMT

What DIS version?

Actually: Looking at your code would below syntax return the same result? Or is the source data set sorted by a second variable?

data pt_all_LAC_curr ;

set A ;

by AID AMEM;

if first.AID;

length All_AMEMs $255;

All_AMEMs=cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")");

run;

Below SQL could work and would be easy to implement using DIS - but it really depends on how your data looks like which I can't tell from you code. Can you provide a representative sample?

proc sql;

create table pt_all_LAC_curr as

select DISTINCT

cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")") as All_AMEMs length=$255

from A

;

quit;

Re: Cannot create SET , BY in SAS DI

ven — Fri, 01 May 2015 22:25:56 GMT

Hi Patrick,

There is actually an else clause which prevent the use of method you described.

I have updated the question as below to show it.

In SAS DI Version: 4.21 or 4.8

proc sort DATA=AIN out=A;

By AID AMEM;

data pt_all_LAC_curr (keep= AID AMEM All_AMEMs) ;
set A ;
by AID AMEM;

length All_AMEMs $255;

if first.AID then do;
retain All_AMEMs;
All_AMEMs=cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")");
end;

else do All_AMEMs=catx(" NEXT VAL ",cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")"));

end;
if last.AID then output;

run;

For example:

INPUT

AID AMEM Plan_mgmt_team_member Participant_Management_Role_Desc

1 5 AA AA_DESC

1 5 BB BB_DESC

1 5 CC CC_DESC

2 6 DD DD_DESC

2 6 EE EE_DESC

2 6 FF FF_DESC

Expected OUTPUT:

AID AMEM All_AMEMs

1 5 AA (AA_DESC) NEXT VAL BB (BB_DESC) NEXT VAL CC (CC_DESC)

2 6 DD (DD_DESC) NEXT VAL EE (EE_DESC) NEXT VAL FF (FF_DESC)

Re: Cannot create SET , BY in SAS DI

Patrick — Sat, 02 May 2015 00:22:37 GMT

You can do what you're asking for by "tricking" DIS and ingest your data step logic using a Splitter; but it's ugly and I wouldn't go for it. The input to the Splitter would need to be a table with target column "All_AMEMs " already existing (but then drop it in the Splitter using the option for input tables) - this needs to be done to overcome the mapping step generated by the Splitter as else the generated string for "ALL_AMEMs" gets wiped out.

In the row selection bit you can write code as below:

by AID AMEM;

length All_AMEMs $255;

retain All_AMEMs;

if first.AID then do;

call missing(All_AMEMs);

end;

All_AMEMs=catx(' ',All_AMEMs,Plan_mgmt_team_member,'(',Participant_Management_Role_Desc,')');

if last.AID

As said: Really ugly and also no more really metadata driven so I don't see a lot of value to go down such a path.

I consider it as acceptable to have user written code for a special case like yours as long as you're using the generated macro variables as far as possible. Something like:

data &_output;

set &_input;

keep &_OUTPUT_keep;

by AID AMEM;

length All_AMEMs $255;

retain All_AMEMs;

All_AMEMs=catx(' ',All_AMEMs,Plan_mgmt_team_member,'(',Participant_Management_Role_Desc,')');

if last.AID then

do;

output;

call missing(All_AMEMs);

end;

run;

I'm attaching the .spk of my sample DIS job - but it has been written with DIS4.9 so not sure if you will be able to import it.

Re: Cannot create SET , BY in SAS DI

ven — Sat, 02 May 2015 01:46:39 GMT

Hi Partick,
Thank you for the reply, as for the package I cannot import it, Can you pl. attach the generated code of your SAS DI job.

Thanks a lot

Re: Cannot create SET , BY in SAS DI

Patrick — Sat, 02 May 2015 06:09:53 GMT

Attached the code of below flow.

Node 1 creates some sample data

Node 2 adds variable "All_AMEMs" to the source for the splitter

Node 3 is the Splitter code creating table Want

Node 4 is the alternative approach using user written code creating table Want_2