BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
deleted_user
Not applicable
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;
  • deleted_user
    Not applicable
    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..
    deleted_user
    Not applicable
    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.
  • deleted_user
    Not applicable
    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...
    Cynthia_sas
    SAS Super FREQ
    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]
    deleted_user
    Not applicable
    Thanks Cynthia. Worked like a charm !

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 6 replies
    • 779 views
    • 0 likes
    • 2 in conversation