Help using Base SAS procedures

Padding values

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Padding values

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


Accepted Solutions
Solution
‎09-30-2013 07:21 AM
Respected Advisor
Posts: 3,799

Re: Padding values

Posted in reply to sasbegginer

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


    All Replies
    Super User
    Posts: 11,343

    Re: Padding values

    Posted in reply to sasbegginer

    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.

    Contributor
    Posts: 33

    Re: Padding values

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

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    Posted in reply to sasbegginer

    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

    Contributor
    Posts: 33

    Re: Padding values

    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

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    Posted in reply to sasbegginer

    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?

    Contributor
    Posts: 33

    Re: Padding values

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

    Business requiement is a little learninig Smiley Happy

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    Posted in reply to sasbegginer

    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

    Contributor
    Posts: 33

    Re: Padding values

    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,,,,,,,,,,,,,,,,,,,,,,

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    Posted in reply to sasbegginer

    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.

    Contributor
    Posts: 33

    Re: Padding values

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

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    Posted in reply to sasbegginer

    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;

         

    Super User
    Posts: 5,429

    Re: Padding values

    Posted in reply to sasbegginer

    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
    Super User
    Posts: 11,343

    Re: Padding values

    Posted in reply to sasbegginer

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

    Respected Advisor
    Posts: 3,156

    Re: Padding values

    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;

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 29 replies
    • 736 views
    • 1 like
    • 6 in conversation