BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zatere
Quartz | Level 8

Hello there,

 

I would ask for assistance on the following. 

 

I have a table with about 200,000 rows represnting information about Journeys. It has columns like JourneyID, VehicleType, WeatherCondition etc.

 

From this table I want to create another table which will have only 1 column. This column will include all the different JourneyIDs on the same row.

 

So, the end result will be a table like the below (of course the table will have about 100,000 values in the single row):

 

Spoiler
JourneyID
'1','2','3','4','5','6','7','8','9',10'

When I run the following code I am getting a table with each JourneyID in different row instead:

 

proc sql;
create table JourneyIDs as select case when VehicleType = 'Car'
then quote(trim(JourneyID), "'") end as JourneyID length = 32767
from Journeys
; quit;

The table I am getting:

 

Spoiler
JourneyID
'1'
'2'
'3',
'4'
'5'
'6'
'7'
'8'
'9'
'10'

I hope it make sense and would appreciate any help.

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

SQL is designed to work with normalised data, and so isn't the best tool for this job.

proc sort data=have nodupkey;
  by journeyid;
run;
data want;
  set have;
  retain jid;
  length jid $2000;
  jid=ifc(_n_=1,journeyid,catx(',',jid,journeyid);
run;

Bear in mind that character strings have a maximum length - hence why keeping data in the normalised way is far better than transposing data up.  Say you have 100k unique records all at 10 characters each, this would not be possible to keep in one long string, but is possible to keep as 100k obs, and far more usable.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

SQL is designed to work with normalised data, and so isn't the best tool for this job.

proc sort data=have nodupkey;
  by journeyid;
run;
data want;
  set have;
  retain jid;
  length jid $2000;
  jid=ifc(_n_=1,journeyid,catx(',',jid,journeyid);
run;

Bear in mind that character strings have a maximum length - hence why keeping data in the normalised way is far better than transposing data up.  Say you have 100k unique records all at 10 characters each, this would not be possible to keep in one long string, but is possible to keep as 100k obs, and far more usable.

Zatere
Quartz | Level 8

Thanks, it does what I was looking for!

 

The reason that I wanted to code in that way is because I want to create a macro list of all those JourneyIDs. 

However, when I use the standard method:

 

proc sql noprint;
select : JourneyID into : JounreyList separated by ','
from have;
quit;

then I am getting an error that:

 

Spoiler
The text expression length (65535) exceeds the maximum length (65534)

so I am not able to use this method because my list is very big.

 

Would you have any other ways to put about 100,000 IDs in a macro list?

 

SuryaKiran
Meteorite | Level 14

Why do you want to add all 100k ID's into a macro or into one variables. This is not the efficient way of doing. What is your requirement that made to you think in this way?

 

Did you try something like 

Where JourneyID in (select distinct JourneyID form have);

Thanks,
Suryakiran
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, I knew what you wanted to use it for, seems like every other post on here now is macro lists. It really is not a good idea.  Macro limits aside, the code needed to process these things is just messy and not needed.  Why do you need the list?  Take two examples:
1) I want to filter another table:

Then use:

select * from sometable where journey_id in (select distinct journey_id from other_table)

Far simpler.

2) I want to print out per journey_id or call other macro:

proc sort data=other_table out=loop nodupkey;
  by journey_id;
run;

data _null_;
  set loop;
  call execute(cats('%print_ds (journey_id=',journey_id,');')));
run;

Any scenario you come up with, there is a better method just using Base SAS methods.

ballardw
Super User

@Zatere wrote:

Thanks, it does what I was looking for!

 

The reason that I wanted to code in that way is because I want to create a macro list of all those JourneyIDs. 

 

Would you have any other ways to put about 100,000 IDs in a macro list?

 


What are you going to do with those 100,000 IDS? When I see someone trying to cram that much information into a single macro variable then it seems like the next step is often trying to use the macro language to do the data processing that data steps or other procedures using a BY statement are designed for.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1989 views
  • 0 likes
  • 4 in conversation