topic Re: Proc Compare for Excel Date Format Question in SAS Procedures

Proc Compare for Excel Date Format Question

haotiansas — Thu, 30 Apr 2015 16:34:27 GMT

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.

Date format – I’d like the output to show the actual date “06/23/2015” instead of “42178”.
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!

Re: Proc Compare for Excel Date Format Question

ballardw — Thu, 30 Apr 2015 17:52:45 GMT

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.

Re: Proc Compare for Excel Date Format Question

haotiansas — Thu, 30 Apr 2015 21:32:36 GMT

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. ;

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. ;

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;

Re: Proc Compare for Excel Date Format Question

ballardw — Thu, 30 Apr 2015 21:54:51 GMT

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 ;