topic Re: Padding values in SAS Procedures

Padding values

sasbegginer — Thu, 26 Sep 2013 14:35:44 GMT

Hi,

I am have a table that looks like this

KEY	VALUE	POSITION
1	3	5
2	33	9
2	55	3
1	76	1
2	44	49
3	21	2

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

key	COMMA
1	76,,,,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

Re: Padding values

ballardw — Thu, 26 Sep 2013 14:48:02 GMT

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.

Re: Padding values

sasbegginer — Thu, 26 Sep 2013 14:59:06 GMT

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

Re: Padding values

Haikuo — Thu, 26 Sep 2013 15:30:34 GMT

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

Re: Padding values

sasbegginer — Thu, 26 Sep 2013 15:37:01 GMT

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

Re: Padding values

Haikuo — Thu, 26 Sep 2013 15:42:06 GMT

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?

Re: Padding values

LinusH — Thu, 26 Sep 2013 15:43:55 GMT

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...?

Re: Padding values

sasbegginer — Thu, 26 Sep 2013 15:51:49 GMT

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

Business requiement is a little learninig

Re: Padding values

Haikuo — Thu, 26 Sep 2013 15:55:23 GMT

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

Re: Padding values

sasbegginer — Thu, 26 Sep 2013 15:58:07 GMT

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

Re: Padding values

Haikuo — Thu, 26 Sep 2013 16:26:12 GMT

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.

Re: Padding values

ballardw — Thu, 26 Sep 2013 17:09:44 GMT

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

Re: Padding values

sasbegginer — Thu, 26 Sep 2013 17:47:51 GMT

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

Re: Padding values

Haikuo — Thu, 26 Sep 2013 18:05:38 GMT

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;

Re: Padding values

Haikuo — Thu, 26 Sep 2013 18:13:42 GMT

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;

Re: Padding values

Haikuo — Thu, 26 Sep 2013 18:19:44 GMT

Given the length of 30, here is another approach without involving Hash(), if you care:

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 sort data=have;

by key;

run;

data want;

array t(30) _temporary_;

do until (last.key);

set have;

by key;

t(position)=value;

end;

comma=cats(of t(*));

call missing(of t(*));

put comma=;

run;

Haikuo

Re: Padding values

ballardw — Thu, 26 Sep 2013 21:19:09 GMT

Better, I was thinking of a similar project and hadn't filled out the details for this topic.

Re: Padding values

Tom — Fri, 27 Sep 2013 02:32:32 GMT

You can use the DSD option of the FILE statement to generate the commas for you.

data want;

array t(30) ;

do until (last.key);

set have;

by key;

t(position)=value;

end;

file dummy temp dlm=',' dsd lrecl=200 ;

put @1 200*' ' @1 (t(*)) (:) @ ;

comma = _file_;

putlog key= comma=;

keep key comma ;

run;

KEY=1 comma=76,,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,

KEY=2 comma=,,55,,,,,,33,,,,,,,,,,,,,,,,,,,,44,,

KEY=3 comma=,21,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Re: Padding values

Haikuo — Fri, 27 Sep 2013 04:05:12 GMT

Hi Tom,

I had to reverse "dummy temp" to "temp dummy" to make it work -:). I believe that dlm=',' can be omitted if using with DSD keyword.

Haikuo

Re: Padding values

Tom — Fri, 27 Sep 2013 04:54:13 GMT

I had previously created a fileref DUMMY by using

FILENAME DUMMY TEMP ;

So the FILE statement then just needs to be

FILE DUMMY DSD ;

If you just use FILE TEMP ... or FILE TEMP DUMMY ... it will try to write a file into the current working directory named 'temp.dat' instead of creating a real temporary file in the WORK folder.

You can also eliminate writing of multiple spaces to clear the _FILE_ buffer in the PUT statement if you use the COL= option on the FILE statement.

FILENAME DUMMY TEMP ;

data want;

array t(30) ;

do until (last.key);

set have;

by key;

t(position)=value;

end;

file dummy dsd col=col;

put @1 (t(*)) (:) ' ' @ ;

comma = substrn(_file_,1,col-1) ;

putlog key= col= comma=;

keep key comma ;

run;