@melligeri wrote:
Sorry about lack of information.
Any help is appreciated, Thank you.
Please write complete and clear specifications for the problem, with several examples (plural), so we don't have to keep asking you for more information. Spend some time on it. Be thorough. Emphasis on COMPLETE and emphasis on CLEAR.
Why not fix it BEFORE transposing?
Let's assume by 'null' you mean missing.
data have ;
input id (val1-val8) ($);
cards;
1 abc efg hij klm npo qrs tuv wxy
2 hdi iijk klm . . . . .
3 kkk iikl . . . . . .
4 klm . . . . . . .
;
If you literally have the string 'null' in the variable values then adjust the code below to account for that.
You could unroll the data out into a "tall" dataset instead of the current "wide" dataset. Then roll it back into the array.
data tall;
set have;
array x val1-val8;
do index=dim(x) to 1 by -1;
value=x[index];
if not missing(value) then output;
end;
keep id value;
run;
data want;
do index=1 by 1 until(last.id);
merge have tall;
by id;
array x val1-val8;
if first.id then call missing(of x[*]);
x[index]=value;
end;
drop index value ;
run;
If the data is large then you might want to make the TALL dataset as a VIEW instead.
Result:
Obs id val1 val2 val3 val4 val5 val6 val7 val8 1 1 wxy tuv qrs npo klm hij efg abc 2 2 klm iijk hdi 3 3 iikl kkk 4 4 klm
If the values are really missing (instead of the silly "null" string in your example) then you could use CATX() to help you. Make sure to define the string variable long enough to hold the values.
data want;
set have;
array x val1-val8 ;
length string $200 ;
string=catx('|',of val8-val1);
do index=1 to dim(x);
x[index]=scan(string,index,'|');
end;
drop index string;
run;
If you really have the "null" text in the fields then remove the trailing "null" text first. You can use COALESCEC() to add it back it you want.
data want;
set have;
array x val1-val8 ;
do index=dim(x) to 1 by -1 while (x[index]='null');
call missing(x[index]);
end;
length string $200 ;
string=catx('|',of val8-val1);
do index=1 to dim(x);
x[index]=coalescec(scan(string,index,'|'),'null');
end;
drop index string;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: