topic Re: Regular data import had a 1 time change in SAS Programming

Regular data import had a 1 time change

appletop — Fri, 27 Nov 2020 09:58:51 GMT

Every month I receive a data set as a flat-file, and import it into SAS. Last month, it arrived with binary variables stored as True / False instead of 1/0. This month it's back to 1/0, with an apology for the annoyance.

I made a custom format like:

proc format;
  invalue truefalseformat
    'True'=1
    'False'=0;
run;

data mydata;
   infile &myfile delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2;
     informat aaa $5.;
     informat bbb truefalseformat.;
   input 
     aaa $
     bbb
    ;
run;

and edited my import script for the relevant variables. But now I need to run the proc format code every SAS session that I want to look at the data.

How can I change my script so that on or after import the variables are actually changed from True/False to 1/0 in the data and so I don't need to run the proc format code every time?

Thanks

Re: Regular data import had a 1 time change

andreas_lds — Fri, 27 Nov 2020 10:19:28 GMT

Untested:

proc format;
  invalue truefalseformat
    'True', '1' = 1
    'False', '0' = 0;
run;

Re: Regular data import had a 1 time change

FreelanceReinh — Fri, 27 Nov 2020 10:35:58 GMT

Hi @appletop,

With the INFORMAT statement you permanently associated the user-defined informat truefalseformat to certain variables, i.e., the name of the informat is contained in the metadata of the dataset and causes problems if it's not found in the available format catalogs (FMTSEARCH= system option).

To avoid these problems, you should have used the informat specification only in the INPUT statement, e.g.,

input aaa :$5. bbb :truefalseformat.;

for list input. I virtually never use the INFORMAT statement.

To remove the (mostly) unnecessary informat from a variable like bbb in the existing dataset, you can use PROC DATASETS:

proc datasets lib=work nolist;
modify mydata;
informat bbb;
quit;

I typically use

informat _all_;

in the PROC DATASETS step to remove all informats.

Re: Regular data import had a 1 time change

ballardw — Fri, 27 Nov 2020 15:07:23 GMT

@andreas_lds wrote:

Untested:

proc format;
  invalue truefalseformat
    'True', '1' = 1
    'False', '0' = 0;
run;

My version for such looks more like

proc format;
  invalue truefalseformat (upcase)
    'TRUE','T','Y','YES', '1' = 1
    'FALSE','F','N','NO', '0' = 0
    ' '=.
    other =_error_
   ;
run;

The upcase helps with poor manual data entry where you get mixed T,t,F,f, fALSE, FalSE and so on. The explicit blank for missing allow use of Other to capture the really stupid data entries, like "?", by throwing invalid data messages to the log.