Proc Compare for Excel Date Format Question

[haotiansas]

Hi all-

I am encountering some issues with the Proc Compare procedure using SAS Studio.

I’ve tried to lay out the two main challenges I’m facing with the Compare procedure and maybe you could provide some guidance.

1.   Date format – I’d like the output to show the actual date “06/23/2015” instead of “42178”.
2.   Y-axis format – I’d like the output under Obs to say “Test 6” instead of “6”

Here's my code:

proc import datafile='/folders/myfolders/iPlan/ExcelCompareWorksheets.xlsx'

                out=work.myexcel

                dbms=xlsx;

                range='NewGoat$C2:CJ89';

run;

proc import datafile='/folders/myfolders/iPlan/ExcelCompareWorksheets.xlsx'

                out=work.myexcel2

                dbms=xlsx;

                range='OldGoat$C2:CJ89';

run;

proc compare base=myexcel compare=myexcel2;

run;

Thanks in advance for reading and offering any guidance!

[ballardw]

Priory to the proc compare you would need to assign a format to display the values as needed. For your dates a format of mmddyy10. would work.

proc datasets library=work nodetails memtype=data;  /* assumes the two sets are in the library work*/

modify myexcel; format <names of your date variables> mmddyy10. ;

modify myexcel2; format <names of your date variables> mmddyy10. ;

quit;

I'm not sure there is a good easy way to replicate the proc compare output and change the OBS to read Test xxx.

[haotiansas]

Hello, thanks for the tip! I used your code and ran into the following errors;

NOTE: Statements not processed because of errors noted above.

57 ! format <Finish> mmddyy10. ;

  _

  22

  76

ERROR 22-322: Syntax error, expecting one of the following: a name, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. 

ERROR 76-322: Syntax error, statement will be ignored.

58 modify myexcel2;

58 ! format <Start> mmddyy10. ;

  _

  22

  76

ERROR 22-322: Syntax error, expecting one of the following: a name, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. 

ERROR 76-322: Syntax error, statement will be ignored.

59 quit;

NOTE: Statements not processed because of errors noted above.

NOTE: The SAS System stopped processing this step because of errors.

My code:

proc import datafile='/folders/myfolders/iPlan/ExcelCompareWorksheets.xlsx'

                out=work.myexcel

                dbms=xlsx;

                range='NewGoat$B2:BJ89';

run;

proc import datafile='/folders/myfolders/iPlan/ExcelCompareWorksheets.xlsx'

                out=work.myexcel2

                dbms=xlsx;

                range='OldGoat$B2:BJ89';

run;

proc datasets library=work nodetails memtype=data;  /* assumes the two sets are in the library work*/

modify myexcel; format <Finish> mmddyy10. ;

modify myexcel2; format <Start> mmddyy10. ;

quit;

proc compare base=myexcel compare=myexcel2;

run;

[ballardw]

Don't put the variable names within <> I put those there to indicate that you put a list of variables and I did not know all of the variables that you want to set the format for. This uses the same syntax as the FORMAT statement in a data step:

Format <list of variable names> <format for those variables> <another list of variables> <format for other variables> ;

Such as:

Format thisdate thatdate mmddyy10. thisnum thatnum theothernum F7.2 ;