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
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
i am able to merge the data, but the order is displaying as below. My expected order is different
ID | Name | support by | owner | App | microvar1 | microvar2 | microvar3 |
You can use RETAIN to re-order the variables in a dataset.
data want;
retain Height Weight Name;
set sashelp.class;
run;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.