topic Re: Formats in SAS Programming

Formats

JibJam221 — Thu, 03 Jan 2019 16:04:11 GMT

PROC IMPORT DBMS=ACCESSCS DATATABLE='RVDEC'

Re: Formats

gamotte — Thu, 03 Jan 2019 14:37:15 GMT

Hello,

You can use the vformat function to check the formats of the elements in an array.

Also, you cannot change the format of a dataset column on the fly. You have to create a new

column and drop the old one.

data mpart;
    format  BDAY REcDate ReTDate datetime.;
    BDAY=datetime();
    RecDate=datetime();
    RetDate=datetime();
    x=1;
run;

data _NULL_;
    set work.mpart;
    array fixed _NUMERIC_;

    call execute('data want; set work.mpart;');

    do over fixed;
        if vformat(fixed)=:"DATETIME" then do;
            varname=vname(fixed);
            call execute(cat('drop ',strip(varname),';'));
            call execute(cat('format N_',strip(varname),' date9.;'));
            call execute(cats('N_',varname,'=datepart(',varname,');'));
        end;
    end;

    call execute('run;');

    stop;
run;

Re: Formats

JibJam221 — Thu, 03 Jan 2019 14:56:47 GMT

Hi! Thanks so much for this,
I receive an error stating File Work.DROP.DATA doesn't exist. However, cannot localize where this error is coming from. Do you have any ideas?

Re: Formats

gamotte — Thu, 03 Jan 2019 15:01:48 GMT

From the error message, i would say that you forgot the semi-colon after 'set work.mpart' in the first call execute. If not, post the complet log to help us diagnose the problem.

Re: Formats

jdwaterman91 — Thu, 03 Jan 2019 15:10:18 GMT

You can use an attrib statement in a data step.

Attrib BDAY REcDate ReTDate Y201 Y22 Y716 Y133 Y876 Y7623 Y8613 Format = Date9.;

Re: Formats

JibJam221 — Thu, 03 Jan 2019 15:17:40 GMT

hi! When using this, the columns listed present '******' instead of the actual date. any ideas on how to get around this issue?

Re: Formats

JibJam221 — Thu, 03 Jan 2019 15:20:09 GMT

Here is the log when using the do loop method

Re: Formats

jdwaterman91 — Thu, 03 Jan 2019 15:23:29 GMT

I would suggest making your columns wider. I'm guessing that it is displaying that way in your dataset due to the columns not being wide enough.

Re: Formats

JibJam221 — Thu, 03 Jan 2019 15:27:05 GMT

Hi again, after looking it up, enterprise 7.1 masks these when ATTRIB is used. However, when using base sas 9.2, I receive and error stating: ERROR: There was a problem with the format so BEST. was used.

As a result, the date variable is not displayed. Any suggestions?

Re: Formats

gamotte — Thu, 03 Jan 2019 15:33:37 GMT

Please post the log as text as some contributors cannot or won't open attached documents.
It would be useful to have the complete log indicating which code has been executed.

For instance, here is the log i obtain :

696
697  data _NULL_;
698      set work.mpart;
699      array fixed _NUMERIC_;
700
701      call execute('data want; set work.mpart;');
702
703      do over fixed;
704          if vformat(fixed)=:"DATETIME" then do;
705              varname=vname(fixed);
706              call execute(cat('drop ',strip(varname),';'));
707              call execute(cat('format N_',strip(varname),' date9.;'));
708              call execute(cats('N_',varname,'=datepart(',varname,');'));
709          end;
710      end;
711
712      call execute('run;');
713
714      stop;
715  run;

NOTE: There were 1 observations read from the data set WORK.MPART.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1   + data want; set work.mpart;
2   + drop BDAY;
3   + format N_BDAY date9.;
4   + N_BDAY=datepart(BDAY);
5   + drop REcDate;
6   + format N_REcDate date9.;
7   + N_REcDate=datepart(REcDate);
8   + drop ReTDate;
9   + format N_ReTDate date9.;
10  + N_ReTDate=datepart(ReTDate);
11  + run;

NOTE: There were 1 observations read from the data set WORK.MPART.
NOTE: The data set WORK.WANT has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Formats

JibJam221 — Thu, 03 Jan 2019 15:39:21 GMT

hi! After running it in Base SAS (as opposed to enterprise guide) I was able to get a successful output!

Thank you so much.

Re: Formats

jdwaterman91 — Thu, 03 Jan 2019 16:22:11 GMT

Since you have datetime variables initially, they have to be converted to date variables for the format to correctly be applied.

Data Want;
Set Have;

Array X _Numeric_;

Do Over X;
X = Datepart(X);
End;

Attrib _Numeric_ Format = Date9.;
/* If you have other numeric variables in your dataset which you do not want to apply the format to, then specify each variable you want the format applied to. */

Run;