Hello,
I have 2 datasets with a lot of variables, that I need to join.
I'm looking for a way to format just one date column without having to reference it in the SELECT statement,.
My reason for this is that if I reference that specific column I cannot use dataset1.* syntax and would be obliged to write down all of the variables.
On the same note, does anyone know of a way in proc sql to insert a column from a second table in a certain position in the first table?
Thank you.
The variable sequence in SQL is determined by the order in the select statement. By using *, you are bound to the original order.
If you only need to change the attribute of one variable, determine the name, and then use PROC DATASETS, MODIFY with ATTRIB to change the format for that variable.
I do not know, if it solves your problem but I use proc sql feedback option to list all columns used in proc sql.
Ex
proc sql feedback;
select a.*
from table1;
quit;
Now check the log and get list of all column and replace it with *
The variable sequence in SQL is determined by the order in the select statement. By using *, you are bound to the original order.
If you only need to change the attribute of one variable, determine the name, and then use PROC DATASETS, MODIFY with ATTRIB to change the format for that variable.
This worked perfectly. Thank you !
For those interested my code looks like this:
proc datasets;
modify dataset1;
attrib Datevar format=monyy.;
run;
quit;
Your problem lies in this:
"I have 2 datasets with a lot of variables"
It is never a good idea - from a programming point of view - to work with lots of variables. To write proper SQL then you should specify every variable you require in the output dataset - this is the point of the select statement. The fact that you have lots of variables is a structural problem - and it seems to be endemic in SAS - where you then have problems coding because of the structure. Trust me, this will not be the first problem you encounter with such a data structure. Consider re-structuring into a form which is beneficial to programming - nothing stops you tranposing the data before reporting it.
Well, you will either have to write a correctly ordered list in SQL by hand, or use an "empty format" statement in a data step.
data want;
format
var3
var1
var2
var4
...
;
set have;
run;
if only some variables need to be ordered, one could conceive of a method to automatically create the list from a dataset extracted from sashelp.vcolumn.
- put the known variable names in a macro variable, in wanted order
- in a data step, read all dataset variables and add only those that do not already appear in the macrovar (thus making the list complete)
- create the select list or the "empty format" from the macro variable
If you're using SAS Enterprise Guide, you might like this custom task:
Copy SAS variable names to the clipboard in SAS Enterprise Guide
It provides a shortcut for adding a list of variable names -- in various formats -- into your program.
proc sql; create table class as select * from sashelp.class; alter table class modify age format=date9.; quit;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: