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

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

Elle — Thu, 04 Aug 2016 10:19:08 GMT

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.

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

RahulG — Thu, 04 Aug 2016 10:25:56 GMT

I do not know, if it solves your problem but I use proc sql feedback option to list all columns used in proc sql.

proc sql feedback;

select a.*

from table1;

quit;

Now check the log and get list of all column and replace it with *

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

Elle — Thu, 04 Aug 2016 10:34:20 GMT

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.

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

Kurt_Bremser — Thu, 04 Aug 2016 10:54:23 GMT

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.

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

RW9 — Thu, 04 Aug 2016 11:47:42 GMT

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.

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

Elle — Thu, 04 Aug 2016 12:36:26 GMT

This worked perfectly. Thank you !

For those interested my code looks like this:

proc datasets;
	modify dataset1;
	attrib Datevar format=monyy.;
run;
quit;

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

Elle — Thu, 04 Aug 2016 12:33:51 GMT

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.

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

Kurt_Bremser — Thu, 04 Aug 2016 12:46:26 GMT

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

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

ChrisHemedinger — Thu, 04 Aug 2016 15:24:20 GMT

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.

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

Ksharp — Fri, 05 Aug 2016 07:27:34 GMT

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