Desktop productivity for business analysts and programmers

SAS automatically reassign format for all columns in a table

Reply
Frequent Contributor
Posts: 80

SAS automatically reassign format for all columns in a table

data have;
  input @1 number $char1. 
        @3 date $char10.;
  datalines;
1 01/01/1990
2 02/02/1992
;
run;

data want;
  input @1 number best1. 
        @3 date DDMMYY10.;
  format date date9.;
  datalines;
1 01/01/1990
2 02/02/1992
;
run;

Codes above to generate datasets 'have' and 'want'.

 

The following codes can be used to explicitly reassign the format of those two columns:

data target;
set have;

format temp1 best1.; temp1 = input(number,best8.); drop number; rename temp1=number;
format temp2 date9.; temp2 = input(date, ANYDTDTE10.); drop date; rename temp2=date;
run;

However, is there a way to ask SAS to automatically reassign formats for all columns in a table?

 

e.g. with the Import Wizard, SAS EG can assign a default Type, Len. etc. (depending whether the column has only numbers, or date values).

Super User
Super User
Posts: 7,392

Re: SAS automatically reassign format for all columns in a table

Well, the problem with your methodology would be what default to apply.  Numbers could be anything from 16 to 1, include signs and decimals.  Characters can contain anything and be up to 2000 long, and dates can come in any number of formats (and could be char or number).  So the quesiton is how are you going to standardise all this?  What are your rules?  What are the error capture necessities etc.  Techinically it is possible to generate the necessary program, however you need to have all your rules in place and for them to cover every eventuality.

 

A more pertinent question is why do you not know what your data is like?  Why the need to change formats and such like after the event, sounds like something earlier in the process is not working, maybe your importing from Excel (as Excel is a poor data medium) and using proc import (a guessing procedure, multiplies the issues you will have with Excel).  So a solution there would be to use a robust data medium (CSV, XML etc.) and write a datastep import program to specifically import the data into the model you need.

Ask a Question
Discussion stats
  • 1 reply
  • 96 views
  • 0 likes
  • 2 in conversation