Help using Base SAS procedures

Data transformation issue...

Reply
N/A
Posts: 0

Data transformation issue...

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
N/A
Posts: 0

Re: Data transformation issue...

Posted in reply to deleted_user
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;
  • N/A
    Posts: 0

    Re: Data transformation issue...

    Posted in reply to deleted_user
    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..
    N/A
    Posts: 0

    Re: Data transformation issue...

    Posted in reply to deleted_user
    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.
  • N/A
    Posts: 0

    Re: Data transformation issue...

    Posted in reply to deleted_user
    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...
    SAS Super FREQ
    Posts: 8,864

    Re: Data transformation issue...

    Posted in reply to deleted_user
    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]
    N/A
    Posts: 0

    Re: Data transformation issue...

    Posted in reply to deleted_user
    Thanks Cynthia. Worked like a charm !
    Ask a Question
    Discussion stats
    • 6 replies
    • 211 views
    • 0 likes
    • 2 in conversation