topic Concatnate values if the variable has data in SAS Procedures

Concatnate values if the variable has data

jerry898969 — Fri, 22 Nov 2013 21:32:11 GMT

I have a data table with 5 vars that I want to do some work with. The names of these vars are Sec1-Sec5

Sec1 Sec2 Sec3 Sec4 Sec5

Test1 Test2 Test3 Test5

Test1

Test2 Test4

With the above data I need to create a field that will tell me which fields are populated.

I want my end result to look like this

1,2,3,5

2,4

What is the best way to approach this?

Thank you

JerryLeBreton — Fri, 22 Nov 2013 22:13:03 GMT

A little array processing is one way to solve this...

data want;

set have;

array Secs[5] sec1-sec5;

length populated $10;

do i = 1 to 5;

if secs ne '' then

populated = catx(',', populated, put(i,1.));

end;

drop i;

run;

PGStats — Sat, 23 Nov 2013 03:26:22 GMT

Or, adding a few tricks of the trade (learned mostly on this Forum) :

data want;

set have;

array S{*} sec:;

length populated $10;

do i = 1 to dim(S);

if not missing(S{i}) then

populated = catx(',', populated, i);

end;

drop i;

run;

Jagadishkatam — Sat, 23 Nov 2013 06:45:20 GMT

alternatively,

data want;

set have;

populated=compress(catx(',',of sec1-sec5),,'a');

run;

Thanks,

Jagadish