BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Elle
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

9 REPLIES 9
RahulG
Barite | Level 11

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 *

Elle
Quartz | Level 8
That's really nice and I'll definitely use it in the future, but with my current data this code becomes quite large and unattractive.
Kurt_Bremser
Super User

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.

Elle
Quartz | Level 8

This worked perfectly. Thank you !

For those interested my code looks like this:

proc datasets;
	modify dataset1;
	attrib Datevar format=monyy.;
run;
quit;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Elle
Quartz | Level 8
Thank you for your response, but I've already done the programming with just the variables I'm interested in. Now I'm just looking to change the output presentation for a client in order to look smooth and clean.
Kurt_Bremser
Super User

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

ChrisHemedinger
Community Manager

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.

 

threecopytasks.png

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Ksharp
Super User
proc sql;
 create table class as
  select * from sashelp.class;
  
 alter table class 
  modify age format=date9.;
quit; 

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!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 2691 views
  • 5 likes
  • 6 in conversation