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

I cannot seem to get this code to work. I need to concatentate rows of "ComboCode" for each ID based on the value of the "contemptrLot" and place into a "root" and "path" variable for up to 5 Lots. So I am creating 10 new variables in an array, root_1-root_5 and path_1-path_5. The contempTrLot will determine which fields to fill (for example, if '3' is the contemptrLot then the concatenating of comboCode should be in root_3 and path_3; also there may be multiple contemptrlots for an id like with ID=480870 that need to have a path and a root). The concatenation is only done when includerow=1. If includerow=0 then the "path_ " fields uses the value below it. What I tried to do is sort descending to work backward from highest contempTrlot . This worked ALMOST but I am getting root_5 and path_5 for IDs that don't have a 5 contemptrlot. So i tried to create a macro variable based on the largest trunclot to place as the end point of the DO loop. This is still not working even though I have a condition in the do loop that states "trunclot < i ".

 

I know this is confusing but if anyone wants to figure out this puzzle I have tried to offer more definitions plus sample data and the result I'm looking for. 

 

 

Definitions:

Root - the highest lot number will be blank in the root_ field but the comboCode for that row should be carried over to the next lowest "Lot" value. This should continue, concatenating all rows so that the row with the lowest lot will have all the codes from its higher lot rows.

 

Path - similar to Root except that the highest "Lot" value is not blank in the "path_" fields but rather has its comboCode. All others contenate as the Lot values decrease.

 

 

 

proc sort data=sample;
by id descending lot;
run;

 

data dataprep.temp ;
set sample;
by id descending lot ;

%global lastlot ;
retain pathx_1 pathx_2 pathx_3 pathx_4 pathx_5;
retain rootx_1 rootx_2 rootx_3 rootx_4 rootx_5;
retain codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

 

/* temporary variables that will be retained for concatenation by row*/

array pathx{5} $30 pathx_1 pathx_2 pathx_3 pathx_4 pathx_5 ;
array rootx{5} $30 rootx_1 rootx_2 rootx_3 rootx_4 rootx_5 ;
array codex{5} $30 codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

 

/*final variables*/

array path{5} $30 path_1 path_2 path_3 path_4 path_5 ;
array root{5} $30 root_1 root_2 root_3 root_4 root_5 ;

 

/*determine the maximum Lot for the ID to place DO loop below (for example since it is in descending order for Lot then the highest lot number should be first. If there isn't a 5 trunclot then the loop should not produce values for root_5 or path_5 */

 

if first.id then call symputx('lastlot',trunclot) ;

 

DO i = 1 TO  /*5*/ &lastlot. ;

/*initialize to missing at the start of each patient id*/
if first.id then do;
codex[i] = "" ;
rootx[i] = "" ;
pathx[i] = "" ;
end ;

if includerow = 1 and contemptrlot = i and trunclot = i then
do;
rootx[i]="" ;
pathx[i]=combocode ;
codex[i]=combocode ;
end;

else if includerow = 1 and contemptrlot = i and trunclot < i then do;

rootx[i] = catx(".",rootx[i], codex[i]) ;
pathx[i] = catx(".",pathx[i], combocode ) ;
codex[i]=combocode ;
end;
else
do;
rootx[i]="" ;
pathx[i]="";
codex[i]="";
end;

 

/*the final variables need to end with a "." otherwise SAS will think two combinations with one period is a numeric field*/
if contemptrlot = i then do;
root[i]=cats(rootx[i],'.') ;
path[i]=cats(pathx[i],'.') ;
end;

 

/*remove the extraneous "." character for blank cells*/

if root[i]='.' then root[i]="" ;
if path[i]='.' then path[i]="" ;


END ;


drop rootx: pathx: codeprior: ;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

success! Just in case someone out there is taking on the puzzle, here is the working code. I'm sure there is an easier way but this works.

 

proc sort data=dataprep.sampleroot ;
by id descending lot;
run;

data dataprep.temp ;
set dataprep.sampleroot ;
by id descending lot ;


retain pathx_1 pathx_2 pathx_3 pathx_4 pathx_5;
retain rootx_1 rootx_2 rootx_3 rootx_4 rootx_5;
retain codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

array pathx{5} $30 pathx_1 pathx_2 pathx_3 pathx_4 pathx_5 ;
array rootx{5} $30 rootx_1 rootx_2 rootx_3 rootx_4 rootx_5 ;
array codex{5} $30 codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

array path{5} $30 path_1 path_2 path_3 path_4 path_5 ;
array root{5} $30 root_1 root_2 root_3 root_4 root_5 ;

retain n ;
first=first.id ;
if first.id = 1 then do;
n=trunclot ;
end;

DO i = 1 TO n ;


/*initialize to missing at the start of each patient id*/
if first.id then do;
codex[i] = "" ;
rootx[i] = "" ;
pathx[i] = "" ;
end ;


if includerow = 1 and contemptrlot = i and trunclot = i then
do;
rootx[i]="" ;
pathx[i]=combocode ;
codex[i]=combocode ;
end;

else if includerow = 1 and contemptrlot = i and trunclot < i then do;
rootx[i] = catx(".",rootx[i], codex[i]) ;
pathx[i] = catx(".",pathx[i], combocode ) ;
codex[i]=combocode ;
end;

else if includerow = 1 and contemptrlot < i and trunclot < i then do;
rootx[i] = catx(".",rootx[i], codex[i]) ;
pathx[i] = catx(".",pathx[i], combocode ) ;
codex[i]=combocode ;
end;

else
do;
rootx[i]="" ;
pathx[i]="";
codex[i]="";
end;


/*if contemptrlot = i then do;*/

root[i]=cats(rootx[i],'.') ;
path[i]=cats(pathx[i],'.') ;

if root[i]='.' then root[i]="" ;
if path[i]='.' then path[i]="" ;


END ;

drop rootx: pathx: codeprior: ;
run;

proc sort data=dataprep.temp;
by id lot;
run;

proc print data=dataprep.temp ;
var id includerow combocode lot contemptrlot path_1 path_2 path_3 path_4 path_5 ;
run;

View solution in original post

3 REPLIES 3
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

sample dataset

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

oops. had to made a code edit for the ID field. Please check code again.

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

success! Just in case someone out there is taking on the puzzle, here is the working code. I'm sure there is an easier way but this works.

 

proc sort data=dataprep.sampleroot ;
by id descending lot;
run;

data dataprep.temp ;
set dataprep.sampleroot ;
by id descending lot ;


retain pathx_1 pathx_2 pathx_3 pathx_4 pathx_5;
retain rootx_1 rootx_2 rootx_3 rootx_4 rootx_5;
retain codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

array pathx{5} $30 pathx_1 pathx_2 pathx_3 pathx_4 pathx_5 ;
array rootx{5} $30 rootx_1 rootx_2 rootx_3 rootx_4 rootx_5 ;
array codex{5} $30 codeprior1 codeprior2 codeprior3 codeprior4 codeprior5 ;

array path{5} $30 path_1 path_2 path_3 path_4 path_5 ;
array root{5} $30 root_1 root_2 root_3 root_4 root_5 ;

retain n ;
first=first.id ;
if first.id = 1 then do;
n=trunclot ;
end;

DO i = 1 TO n ;


/*initialize to missing at the start of each patient id*/
if first.id then do;
codex[i] = "" ;
rootx[i] = "" ;
pathx[i] = "" ;
end ;


if includerow = 1 and contemptrlot = i and trunclot = i then
do;
rootx[i]="" ;
pathx[i]=combocode ;
codex[i]=combocode ;
end;

else if includerow = 1 and contemptrlot = i and trunclot < i then do;
rootx[i] = catx(".",rootx[i], codex[i]) ;
pathx[i] = catx(".",pathx[i], combocode ) ;
codex[i]=combocode ;
end;

else if includerow = 1 and contemptrlot < i and trunclot < i then do;
rootx[i] = catx(".",rootx[i], codex[i]) ;
pathx[i] = catx(".",pathx[i], combocode ) ;
codex[i]=combocode ;
end;

else
do;
rootx[i]="" ;
pathx[i]="";
codex[i]="";
end;


/*if contemptrlot = i then do;*/

root[i]=cats(rootx[i],'.') ;
path[i]=cats(pathx[i],'.') ;

if root[i]='.' then root[i]="" ;
if path[i]='.' then path[i]="" ;


END ;

drop rootx: pathx: codeprior: ;
run;

proc sort data=dataprep.temp;
by id lot;
run;

proc print data=dataprep.temp ;
var id includerow combocode lot contemptrlot path_1 path_2 path_3 path_4 path_5 ;
run;

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
  • 3 replies
  • 1159 views
  • 0 likes
  • 1 in conversation