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;
