BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ballardw
Super User

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

Haikuo
Onyx | Level 15

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

Tom
Super User Tom
Super User

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

Haikuo
Onyx | Level 15

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

Tom
Super User Tom
Super User

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;

Haikuo
Onyx | Level 15

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

data_null__
Jade | Level 19

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.

Haikuo
Onyx | Level 15

No wonder when I play with "filename" statement, both "dummy" and "temp" turn blue in SAS 9.3.

Like always, Thank you, John.

sasbegginer
Calcite | Level 5

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.

sasbegginer
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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.

sasbegginer
Calcite | Level 5

Hi,

It is the 8th value as the the first position is the position before the first comma

position1,position2,etc

Haikuo
Onyx | Level 15

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.

sasbegginer
Calcite | Level 5

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(*));

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.
  • 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
    • 2172 views
    • 1 like
    • 6 in conversation