Better, I was thinking of a similar project and hadn't filled out the details for this topic.
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
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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
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
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;
Thanks, Tom. Still so much stuff to learn. I was scared when I saw the manual volume for just "file, infile, put, input and filename" statement. Wondering if I could ever get the hang of it.
Haikuo
In the case of FILENAME both TEMP and DUMMY are device types and Tom used DUMMY as the "fileref" which I suppose is somewhat confusing especially then the FILENAME statement is omitted.
I often use DUMMY as the fileref when I use FILEVAR and the fileref is just a place holder in the FILE/INFILE statements.
No wonder when I play with "filename" statement, both "dummy" and "temp" turn blue in SAS 9.3.
Like always, Thank you, John.
Great thanks for all comments. I have a good idea of how to implment it now with a few minor tweeks. Great starting point to learn about arrays and hashing.
Hi,
After more testing with the code this morning i am getting values put in wrong position if we have multiple value in the string. For example
If i use the following code
options missing=,;
data have;
input KEY$ VALUE POSITION;
cards;
1 3 5
2 33 9
2 55 3
1 76 1
1 77 2
2 44 29
3 21 2
2 44 29
2 66 26
;
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;
I get the following values
7677,,3,,,,,,,,,,,,,,,,,,,,,,,,,
,,55,,,,,33,,,,,,,,,,,,,,,,66,,44,
,21,,,,,,,,,,,,,,,,,,,,,,,,,,,,
As you can see value 33 is shifted to position 8 when it should be position 9. Any ideas why? I cant figure it out. Thanks in advance
Maybe I counted wrong, but from what you presented, '33' is the 9th object on the row. It has 1X'55' and 7x',' in front of it, which makes '33' the 9th.
Hi,
It is the 8th value as the the first position is the position before the first comma
position1,position2,etc
Data_null_'s suggestion is right on the money. In this case, you can't use ',' as missing symbol. Please follow his suggestion, and make sure you reverse the options setting to the default if you haven't done so.
I am using the below code but i am getting diffrent results in enterprise guide and DI studio. In DI studio i do not see the comma seperated values i only see the value ann no commas but in EG i see the comma seperated value as expected. Any ideas why?
array t(50) _temporary_;
do until (last.key);
set bmv_sorted;
by key;
t(position)=value;
end;
bespoke_string=compress(catx(',',of t(*)),".","");
call missing(of t(*));
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
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.
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.