SAS automatically reassign format for all columns in a table

ayin — Tue, 25 Jul 2017 07:21:48 GMT

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).

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

RW9 — Tue, 25 Jul 2017 07:56:21 GMT

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.

topic SAS automatically reassign format for all columns in a table in SAS Enterprise Guide

SAS automatically reassign format for all columns in a table

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