Formats

[JibJam221]

PROC IMPORT DBMS=ACCESSCS DATATABLE='RVDEC'

[gamotte]

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;

[JibJam221]

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?

[gamotte]

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.

[JibJam221]

Here is the log when using the do loop method

[gamotte]

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

[JibJam221]

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

Thank you so much.

[jdwaterman91]

You can use an attrib statement in a data step.

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

[JibJam221]

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

[jdwaterman91]

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.

[JibJam221]

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?

[jdwaterman91]

 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;