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)
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:
1;
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.
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;
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)
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:
1;
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.
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
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
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.