topic Re: Replacing flags with variable names and removing leading character in SAS Programming

Replacing flags with variable names and removing leading character

bknitch — Mon, 29 Jun 2020 21:26:38 GMT

I have a data set that's been transposed already. I'm trying to find a way to replace the 1's with the numeric value it corresponds to in the variable field. I'm trying to un-transpose the variable and make the table long and skinny rather than wide. I'm also trying to replace the variable names with one name as 'COND_C'.

There are roughly 90 variables that start at CC1 and end in CC189. I tried it with an if statement but it did not capture it correctly.

ID	CC1	CC2	CC6	CC8	CC9	CC100	CC108
123	1	0	0	0	0	1	0
124	0	1	0	0	0	1	0
125	1	0	0	1	0	0	0

This is what I would like the output to look like.

ID	COND_C
123	1
123	100
124	2
124	100
125	1
125	8

Re: Replacing flags with variable names and removing leading character

Reeza — Mon, 29 Jun 2020 21:58:50 GMT

Do a wide to long proc transpose and filter out all the 0's.

proc transpose data=have out=want(where=(col1 ne 0) rename = _name_ = cond_c);
by ID;
var CC1-CC189;
run;

Re: Replacing flags with variable names and removing leading character

r_behata — Mon, 29 Jun 2020 21:41:35 GMT

data have;
input ID$ CC1	CC2	CC6	CC8	CC9	CC100	CC108;
cards;
123 1 0 0 0 0 1 0
124 0 1 0 0 0 1 0
125 1 0 0 1 0 0 0
;
run;

data want;
set have;
array C_[*]  CC:;

do i = 1 to dim(C_);
	if C_[i] > 0 then 
		COND_C=input(compress(vname(C_[i]),'CC'),8.);
	else continue;
	output;
end;

drop CC:;
run;

Re: Replacing flags with variable names and removing leading character

bknitch — Mon, 29 Jun 2020 21:58:08 GMT

This was perfect, thank you for this!