BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Putt_Ka
Calcite | Level 5

Greetings again all!

I'm using the ODS Tagset ExcelXP and ProcPrint multiple tables to multiple sheets in excel (well first to XML then Excel).  My problem is that when my dataset comes back blank no sheet is written, and my work book is considerably smaller.  To fix this problem I use the following MACRO (thnx SAS forum!):

%macro IFz1(data=&syslast) /des= 'if Zero make 1 obs' ;

data &data ;

   if nobs then stop ;

   output ;

   modify &data nobs=nobs ;

   stop ;

run ;

%mend  IFz1;

This inserts one observation when there are no observations.  The problem is that it can't add an observation when a date is involved; example:

proc sql;

         CREATE TABLE foo AS

         SELECT

               date1 format mmddyy11. AS date2,

          FROM foo2;

%IFz1 will then run but come back with ERROR: Width specified for format MMDDYY is invalid, when I comment out the "format mmddyy11." it works fine and inserts a blank observation.  I believe that my column "date1" in my example is a DateTime data type.  If I change the format to mmddyyyy the column says: "5at1946" and is a "custom" data type in excel. 

Has anyone encountered this before, and is there a work around?  I would love to know!

Loving the SAS community so far, I hope to contribute at some point Smiley Happy

Thanks again!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

According to the documentation for SAS 9.3 mmddyy only has a range of 2 to 10 allowed. Change the 11 to 10 and it should work.

View solution in original post

3 REPLIES 3
ballardw
Super User

According to the documentation for SAS 9.3 mmddyy only has a range of 2 to 10 allowed. Change the 11 to 10 and it should work.

SASKiwi
PROC Star

If you check out the documentation:

http://support.sas.com/documentation/cdl/en/leforinforref/63324/HTML/default/viewer.htm#p1og22ny80wq...

You will see that the maximum length for the mmddyy format is 10, hence the error you are getting. Also specify a format in SQL like so: select date1 format = mmddyy10. as date2

Putt_Ka
Calcite | Level 5

Magic!

Thanks all!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 13074 views
  • 3 likes
  • 3 in conversation