DATA Step, Macro, Functions and more

How to select variables from dataset that have common suffix

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

How to select variables from dataset that have common suffix

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!


Accepted Solutions
Solution
‎01-24-2016 08:01 PM
Super User
Posts: 17,963

Re: How to select variables from dataset that have common suffix

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


All Replies
Super User
Posts: 17,963

Re: How to select variables from dataset that have common suffix

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

Contributor
Posts: 31

Re: How to select variables from dataset that have common suffix

Thank you @Reeza

Solution
‎01-24-2016 08:01 PM
Super User
Posts: 17,963

Re: How to select variables from dataset that have common suffix

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.

Contributor
Posts: 31

Re: How to select variables from dataset that have common suffix

Hello @Reeza

 

I did that. it works 

 

Thank you!

Trusted Advisor
Posts: 1,131

Re: How to select variables from dataset that have common suffix

[ Edited ]

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
Contributor
Posts: 31

Re: How to select variables from dataset that have common suffix

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

Trusted Advisor
Posts: 1,131

Re: How to select variables from dataset that have common suffix

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
Contributor
Posts: 31

Re: How to select variables from dataset that have common suffix

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

Super User
Posts: 17,963

Re: How to select variables from dataset that have common suffix

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. 

 

 

Contributor
Posts: 31

Re: How to select variables from dataset that have common suffix

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.

Trusted Advisor
Posts: 1,131

Re: How to select variables from dataset that have common suffix

I agree with @Reeza and you, i missed the separated by.

Thanks for checking back
Thanks,
Jag
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 490 views
  • 3 likes
  • 3 in conversation