BookmarkSubscribeRSS Feed
radha009
Quartz | Level 8

creating a dataset with proc sql from two tables. In onetable i have varlist. column names. i want to display the column names as per our req. order.

 

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where
  libname = "PATCH" and
  memname = "table1" and
  name like '%CHG%';
quit;

Expected:

IDNamesupport bymicroVar1microvar2microvar3ownerApp
1abcayesyesyesyr
2xyzdnononohd
3ertfyesyesyeshf
4saprnononohc
        
table 1:       
IDNamesupportbymicroVar1    
1abcayes    
2xyzdno    
3ertfyes    
4saprno    
        
table 2:       
IDNamemicrovar2microvar3ownerApp  
1abcyesyesyr  
2xyznonohd  
3ertyesyeshf  
4sapnonohc  

 

The microvar name is displaying from the varlist. How do i put all in one dataset

4 REPLIES 4
Kurt_Bremser
Super User

That's a simple data step merge by id name; that varlist stuff is not necessary.


@radha009 wrote:

creating a dataset with proc sql from two tables. In onetable i have varlist. column names. i want to display the column names as per our req. order.

 

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where
  libname = "PATCH" and
  memname = "table1" and
  name like '%CHG%';
quit;

Expected:

ID Name support by microVar1 microvar2 microvar3 owner App
1 abc a yes yes yes y r
2 xyz d no no no h d
3 ert f yes yes yes h f
4 sap r no no no h c
               
table 1:              
ID Name supportby microVar1        
1 abc a yes        
2 xyz d no        
3 ert f yes        
4 sap r no        
               
table 2:              
ID Name microvar2 microvar3 owner App    
1 abc yes yes y r    
2 xyz no no h d    
3 ert yes yes h f    
4 sap no no h c    

 

The microvar name is displaying from the varlist. How do i put all in one dataset


 

radha009
Quartz | Level 8

i am able to merge the data, but the order is displaying as below. My expected order is different

 

IDNamesupport byownerAppmicrovar1microvar2microvar3
SuryaKiran
Meteorite | Level 14

You can use RETAIN to re-order the variables in a dataset.

 

data want;
retain Height Weight Name;
set sashelp.class;
run;
Thanks,
Suryakiran
Kurt_Bremser
Super User

Do you need that somehow automated?

I use an "empty" format statement (only variable names, no formats) to force an order of variables.

But frankly, this is a non-issue. SAS adresses variables by name, so the horizontal position in the dataset is irrelevant. When you need reports or eports or such ordered, force the order there.

 

 

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
  • 4 replies
  • 2002 views
  • 2 likes
  • 3 in conversation