Help using Base SAS procedures

How to format dates in PROC SQL outside the SELECT statement ?

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

How to format dates in PROC SQL outside the SELECT statement ?

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.

 


Accepted Solutions
Solution
‎08-04-2016 08:30 AM
Super User
Posts: 6,938

Re: How to format dates in PROC SQL outside the SELECT statement ?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Regular Contributor
Posts: 241

Re: How to format dates in PROC SQL outside the SELECT statement ?

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 *

Contributor
Posts: 38

Re: How to format dates in PROC SQL outside the SELECT statement ?

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.
Solution
‎08-04-2016 08:30 AM
Super User
Posts: 6,938

Re: How to format dates in PROC SQL outside the SELECT statement ?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 38

Re: How to format dates in PROC SQL outside the SELECT statement ?

[ Edited ]

This worked perfectly. Thank you !

For those interested my code looks like this:

proc datasets;
	modify dataset1;
	attrib Datevar format=monyy.;
run;
quit;
Super User
Super User
Posts: 7,401

Re: How to format dates in PROC SQL outside the SELECT statement ?

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.

Contributor
Posts: 38

Re: How to format dates in PROC SQL outside the SELECT statement ?

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.
Super User
Posts: 6,938

Re: How to format dates in PROC SQL outside the SELECT statement ?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Community Manager
Posts: 2,761

Re: How to format dates in PROC SQL outside the SELECT statement ?

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

Super User
Posts: 9,681

Re: How to format dates in PROC SQL outside the SELECT statement ?

proc sql;
 create table class as
  select * from sashelp.class;
  
 alter table class 
  modify age format=date9.;
quit; 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 422 views
  • 5 likes
  • 6 in conversation