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
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
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.
Corrected and added another exmple. Applogies and thanks for your response
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
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
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?
Great thanks that works...how i would i limit the padding of the extra commas?
Business requiement is a little learninig
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
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,,,,,,,,,,,,,,,,,,,,,,
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.
No positions greater than 30. I have validation on the job that builds the table not to allow this
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;
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...?
Setting option missing=' '; is another way to prevent getting . in the string.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.