topic Re: Data transformation issue... in SAS Procedures

Data transformation issue...

deleted_user — Tue, 30 Dec 2008 18:44:25 GMT

Dataset abc have 10 columns.
Dataset xyz have these 10 columns listed as rows repeating by primarykey(PK)
Need to fill the dataset abc's 10 columns with either 'Yes' (if the PK in xyz has it listed as an observation) or 'No' if not.

PK _Name_
100 col1
100 col3
100 col4
100 col5
200 col2
200 col9
300 col2
300 col10
etc...

Desired Output:

PK col1 col2 col3 col4 col5 col6 col7 col8 col9 col10

100 Yes No Yes No Yes No No No No No
200 No No No Yes No No No No Yes No
300 No No No No No No No No No Yes

Re: Data transformation issue...

deleted_user — Tue, 30 Dec 2008 19:21:22 GMT

data abc(drop=_name_);
array cols

$ 3 col1-col10 ;
call pokelong(repeat('No ', dim(cols)), addrlong(cols[1]), 3*dim(cols));
do until(last.pk);
set xyz(keep=pk _name_);
by pk;
cols[input(substr(_name_,4), 2.)] = 'Yes';
end;
run;

Re: Data transformation issue...

deleted_user — Tue, 30 Dec 2008 19:40:59 GMT

Ran the above pgm and got the below
SAS ERROR: The variable _name_ in the DROP, KEEP, or RENAME list has never been referenced.
Also, _name_ is in abc dataset but not in xyz.
Column names potentially in real scenario are not like col1, col2 but like abvcsd, sdkfjskd etc., Any better ways to handle this (apart from array declaration)?, please comment..

Re: Data transformation issue...

deleted_user — Wed, 31 Dec 2008 04:41:03 GMT

i don't know why, but please try the following instead
/*
data xyz;
input pk _name_ $;
datalines;
100 col1
100 col3
100 col4
100 col5
200 col2
200 col9
300 col2
300 col10
400 abcde
400 rxybc
;
proc sql noprint;
select distinct _name_ into :columns separated by ' ' from xyz;
quit;
data abc(drop=i _name_);
array cols

$ 3 &columns;
call pokelong(repeat('No ', dim(cols)), addrlong(cols[1]), 3*dim(cols));
do until(last.pk);
set xyz(keep=pk _name_);
by pk;
do i=1 to dim(cols);
if _name_ = vname(cols[ i ]) then cols[ i ] = 'Yes';
end;
end;
run;
*/
If it doesn't work, then this problem is beyond my capacity.

Re: Data transformation issue...

deleted_user — Wed, 31 Dec 2008 15:25:28 GMT

Getting only 1 ERROR now: ERROR: Illegal reference to the array cols.
regarding the below statement:
if _name_ = vname(cols) then cols = 'Yes';
Any Ideas...why...

Re: Data transformation issue...

Cynthia_sas — Wed, 31 Dec 2008 17:08:29 GMT

Hi:
If you read the documentation on using arrays, (the section entitled "Array Reference Statement" )
http://support.sas.com/documentation/cdl/en/lrdict/59540/HTML/default/a000203460.htm
You will find that array references are of the form: [pre]array-name(subscript)[/pre]

Also, a usage note. There is a HUGE warning in the documentation about the use of POKELONG, which I quote here in its entirety:

"CAUTION:
The CALL POKELONG routine is intended only for experienced programmers in specific cases. If you plan to use this routine, use extreme care both in your programming and in your typing. Writing directly into memory can cause devastating problems. It bypasses the normal safeguards that prevent you from destroying a vital element in your SAS session or in another piece of software that is active at the time."

My overall recommendation is that you read the documentation on ARRAY processing and on using FIRST. and LAST. by variable processing as well as some Macro processing basics. One possible alternate approach is shown below.

cynthia
[pre]
data xyz;
infile datalines;
input pk _name_ $;
return;
datalines;
100 kermit
100 bigbird
100 oscar
100 elmo
200 gonzo
200 ernie
300 gonzo
300 count
400 kermit
400 rowlf
500 elmo
500 oscar
500 bert
500 cookie
;
run;

proc sql noprint;
select distinct _name_ into :columns separated by ' '
from xyz;
quit;

data abc_new(drop=i _name_);
set xyz;
by pk;

array cols{*} $3 &columns;
retain numfound 0 &columns;
** reinitialize array to 'No' for first of group;
if first.pk then do;
do i = 1 to dim(cols) by 1;
cols(i) = 'No';
end;
numfound = 0;
end;

** check every obs against list of var names in array;
do i=1 to dim(cols);
if _name_ = vname(cols(i)) then do;
cols(i) = 'Yes';
numfound + 1;
end;
end;

** if last of group, then output;
if last.pk then output;
run;

proc print data=abc_new;
title 'With regular array processing';
run;
[/pre]

Re: Data transformation issue...

deleted_user — Wed, 31 Dec 2008 17:35:00 GMT

Thanks Cynthia. Worked like a charm !