Cannot create SET , BY in SAS DI

[ven]

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)

[Patrick]

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;

[ven]

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)

[Patrick · Accepted solution]

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.

[ven]

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

[Patrick]

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