DATA Step, Macro, Functions and more

Create a variable with case when

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Create a variable with case when

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

 


Accepted Solutions
Solution
‎03-12-2018 01:43 PM
Super User
Super User
Posts: 9,227

Re: Create a variable with case when

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


All Replies
Solution
‎03-12-2018 01:43 PM
Super User
Super User
Posts: 9,227

Re: Create a variable with case when

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.

Occasional Contributor
Posts: 13

Re: Create a variable with case when

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?

 

Super Contributor
Posts: 478

Re: Create a variable with case when

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
Super User
Super User
Posts: 9,227

Re: Create a variable with case when

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.

Super User
Posts: 13,084

Re: Create a variable with case when


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 121 views
  • 0 likes
  • 4 in conversation