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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.