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

Hello to whomever can help me out

 

i have a data like following

Capture.PNG

I want to select those variables that have _return suffix.

Try to run the following code:

proc sql outobs=25;
	select *
	from portfolio (keep=:return);

 but it does not work because the name is not valid.

 

Please help.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Change your naming structure to RETURN_VAR would make this possible. 

 

Additionally, look into Variable Labels vs Variable Names as you can use the labels to make your reporting more versatile and then have more flexibility in your naming conventions.

View solution in original post

11 REPLIES 11
Reeza
Super User

There is no shortcut way to reference variables with a common suffix, only a common prefix. 

Reeza
Super User

Change your naming structure to RETURN_VAR would make this possible. 

 

Additionally, look into Variable Labels vs Variable Names as you can use the labels to make your reporting more versatile and then have more flexibility in your naming conventions.

BingL
Obsidian | Level 7

Hello @Reeza

 

I did that. it works 

 

Thank you!

Jagadishkatam
Amethyst | Level 16

you may try it by selecting the list of variable names ending with common suffix into a macro variable and then calling the macro variable will keep only those variables.

Like

proc sql;
select distinct name into: names from dictionary.columns where lowcase(memname)='portfolio' and name like 'RETURN%';
quit;

proc sql outobs=25;
select *
from portfolio (keep=&names);
quit;

Thanks,
Jag
BingL
Obsidian | Level 7

Hello @Jagadishkatam

 

thank you for trying to help

I run the code you provided. Please see below:

proc sql;
	select distinct name
	into :names 
	from dictionary.columns
	where lowcase(memname)='portfolio' 
		and name like 'Return%';

proc sql outobs=20;
	select *
	from portfolio (keep=&names);

Then I got error message. Please see below:

Capture.PNG

Jagadishkatam
Amethyst | Level 16
Thank you for trying my code, could you please change the case of the text(return) in the where condition

proc sql;
select distinct name
into :names
from dictionary.columns
where lowcase(memname)='portfolio'
and upcase(name) like '%RETURN';
Thanks,
Jag
BingL
Obsidian | Level 7

Hello @Jagadishkatam

 

Thank you for trying to help

now your code has no error, but the result is  not very ideal. Please see the screenshot. I want all the variables with _return suffix to be selected, but your program only select one varaible in the PROC SQL process.

 

Capture.PNG

Reeza
Super User

You should be able to help debug this if you're using these methodologies - I think three of your recent questions are along the same lines here - using the SASHELP tables to generate a dynamic variable list. 

 

This is why the SEPARATED BY is included, otherwise SQL will only return a single value. 

 

 

BingL
Obsidian | Level 7

Hello @Reeza

You are right SEPERATED BY is the key to his code.

now it all worked out!

 

Thank you all for your wonderful helps and effort.

Jagadishkatam
Amethyst | Level 16
I agree with @Reeza and you, i missed the separated by.

Thanks for checking back
Thanks,
Jag

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
  • 11 replies
  • 12094 views
  • 3 likes
  • 3 in conversation