BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasbegginer
Calcite | Level 5

Hi,

I am have a table that looks like this

KEYVALUEPOSITION
135
2339
2553
1761
24449
3212

I need to create another table based on the above that table that creates a column that takes the position and the value and inserts it in to a comma sepearated value. So value 3 will be inserted in to position 5 where each comma represents a position. The outout table will look something like this. The comma field should be padded to 30 commas

keyCOMMA
176,,,,3,,,,,,,,,,,,,,,,,,,,,,,,
2,,55,,,,,,33,,,,,,,,,,,,,,,,,49,
3,21,,,,,,,,,,,,,,,,,,,,,,,,,,

Any ideas on how i would even approach this? I have tried substr and scan but to avail. Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

The problem is not dups your program don't care about dups.

The problem is you are using the missing =, (comma).    You want the comma "between" the array elements not as values for the elements that are missing.

Think of using default for missing and CATX(',',of array

  • )) and then removing (compress or transtrn) the dots.  Or use the FILE magic method.
  • View solution in original post

    29 REPLIES 29
    ballardw
    Super User

    You may need to clean up your example. The example data on top does not contain a key 11 but in the bottom it looks sort of like what you're requesting for the first Key 1 . But the second key 1 with value of 76 wasn't applied and the output for Key 11 with a 1 in position 1 isn't in the top at all.

    It may help to provide more rows of example data.

    And I wouldn't be surprised if someone comes up with a proc transpose solution as it appears you are intending to combine rows.

    sasbegginer
    Calcite | Level 5

    Corrected and added another exmple. Applogies and thanks for your response

    Haikuo
    Onyx | Level 15

    Try this one:

    data have;

    input KEY$  VALUE POSITION;

    cards;

    1     3     5

    2     33    9

    2     55    3

    1     76    1

    2     44    49

    3     21    2

    ;

    proc sql;

      select max(position) into :dim trimmed from have;

    create table h1 as

      select distinct key from have;

    quit;

    data want;

    array t(&dim.) _temporary_;

    length comma $1000;

       if _n_=1 then do;

         if 0 then set have;

           declare hash h(dataset:'have', multidata:'y');

           h.definekey('key');

           h.definedata(all:'y');

           h.definedone();

          end;

          set h1;

            do rc=h.find() by 0 while (rc=0);

            t(POSITION)=value;

            rc=h.find_next();

          end;

           comma=catx(',',of t(*));

           call missing(of t(*));

           put comma=;

    drop value position rc;

    run;

        

    Haikuo

    sasbegginer
    Calcite | Level 5

    Hi,

    Many thanks for this...this is the result i am getting

    76,.,.,.,3,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.

    .,.,55,.,.,.,.,.,33,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,44

    .,21,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.,.

    How can i stop the dots inbetweeen the commas? Also need to limit it to 30 commas.

    Thanks in advance

    Haikuo
    Onyx | Level 15

    Yeah, you need to nest another function:

    comma=tranwrd(catx(',',of t(*)),'.','');

    Haikuo

    And how can you limit to 30 commas when you have position=49?

    sasbegginer
    Calcite | Level 5

    Great thanks that works...how i would i limit the padding of the extra commas?

    Business requiement is a little learninig Smiley Happy

    Haikuo
    Onyx | Level 15

    Please define "extra".

    1. If it means more than 30, then you can't have a position that is greater than 30.

    2. Or if it means anything beyond the last number then please confirm and modify your first post to reflect that.

    Haikuo

    sasbegginer
    Calcite | Level 5

    Hi,

    What i mean is so after the last position an extra x amount of commas should be inserted until the total number of commas reaches thirty. So for example,

    1,,,,2

    would become

    1,,,,2,,,,,,,,,,,,,,,,,,,,,,

    Haikuo
    Onyx | Level 15

    I have to ask you again: do you have positions that are greater than 30? if yes, what do you want to do with them? Knowing your data and your rules will have a direct impact on the complexity of the code.

    sasbegginer
    Calcite | Level 5

    No positions greater than 30. I have validation on the job that builds the table not to allow this

    Haikuo
    Onyx | Level 15

    If that is the case, then the code can be easily tweaked, actually it becomes simpler:

    options missing=.;

    data have;

    input KEY$  VALUE POSITION;

    cards;

    1     3     5

    2     33    9

    2     55    3

    1     76    1

    2     44    29

    3     21    2

    ;

    proc sql;

    create table h1 as

      select distinct key from have;

    quit;

    data want;

    array t(30) _temporary_;

    length comma $1000;

       if _n_=1 then do;

         if 0 then set have;

           declare hash h(dataset:'have', multidata:'y');

           h.definekey('key');

           h.definedata(all:'y');

           h.definedone();

          end;

          set h1;

            do rc=h.find() by 0 while (rc=0);

            t(POSITION)=value;

            rc=h.find_next();

          end;

           comma=compress(catx(',',of t(*)),'.');

           call missing(of t(*));

           put comma=;

           drop value position rc;

    run;

         

    LinusH
    Tourmaline | Level 20

    The dots are representing numerical missing values. When converting to char, you can compress them:

    charval = compress(numval,'.')

    Curious about the business requirement for this exercise, looks quite weird...?

    Data never sleeps
    ballardw
    Super User

    Setting option missing=' '; is another way to prevent getting . in the string.

    Haikuo
    Onyx | Level 15

    missing=' ' does not work with catenation functions as such catx() , as it ignores blank strings. However, a similar suggestion to yours can make the code more efficient and simpler: options missing=,;

    options missing=,;

    data have;

    input KEY$  VALUE POSITION;

    cards;

    1     3     5

    2     33    9

    2     55    3

    1     76    1

    2     44    29

    3     21    2

    ;

    proc sql;

    create table h1 as

      select distinct key from have;

    quit;

    data want;

    array t(30) _temporary_;

    length comma $1000;

       if _n_=1 then do;

         if 0 then set have;

           declare hash h(dataset:'have', multidata:'y');

           h.definekey('key');

           h.definedata(all:'y');

           h.definedone();

          end;

          set h1;

            do rc=h.find() by 0 while (rc=0);

            t(POSITION)=value;

            rc=h.find_next();

          end;

    /*    comma=compress(catx(',',of t(*)),'.');*/

    comma=cats(of t(*));

           call missing(of t(*));

           put comma=;

           drop value position rc;

    run;

    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
    • 29 replies
    • 2154 views
    • 1 like
    • 6 in conversation