SAS Truncating on PROC Import

1800bigk — Thu, 01 Sep 2011 15:12:45 GMT

Hi, I apologive in advance for the long post but I have exhausted all my resources at work and have not found an answer on google so here it goes. I have a macro called TXT_TABTXT3 that lets me read in files from a start week to an end week. The problem I am having is during the process SAS is cutting off my data. My old work around for this was to just put a 99999999999999999999999 or zzzzzzzzzzzzzzzzzzzzzzzzzzzz at the top of each column to trick SAS into thinking my data was that long. Now I cannot do that for a number of reasons so I need to fix my macro. I want to modify the macro so it doesn't do this and there are too many columns for me to manually list each variable name and specify formats plus I want to be able to use this macro with multiple data sources that have different column headings. I never had training in SAS I just picked up things at work from co workers so I don't know the language like I should. The macro was actually given to me from SAS. Below is the macro and I am hoping there is a few lines of code that I can add to fix this issue. Thanks in advance.

OPTIONS NOLABEL;

%MACRO IMPORTYR (IN_LOC,MN,MX,Y);

%PUT &IN_LOC,&MN,&MX,&Y;

%DO I = &MN %TO &MX;

%IF &I LE 9 %THEN %DO;

%PUT &I "&IN_LOC&Y.0&I..CSV";

PROC IMPORT OUT = X&I

DATAFILE = "&IN_LOC&Y.0&I..CSV"

DBMS = CSV REPLACE;

GETNAMES = YES;

DATAROW = 2;

RUN;

%END;

%ELSE %DO;

%PUT &I "&IN_LOC&Y&I..CSV";

PROC IMPORT OUT = X&I

DATAFILE = "&IN_LOC&Y&I..CSV"

DBMS = CSV REPLACE;

GETNAMES = YES;

DATAROW = 2;

RUN;

%END;

%IF &MN = &I %THEN %DO;

DATA TMP&Y;

SET X&I;

IF &I LE 9 THEN FILEID = &Y.0&I;

ELSE FILEID = &Y&I;

RUN;

%END;

%ELSE %DO;

DATA XYZ;

SET X&I;

IF &I LE 9 THEN FILEID = &Y.0&I;

ELSE FILEID = &Y&I;

RUN;

PROC APPEND FORCE

BASE = TMP&Y

DATA = XYZ;

RUN;

PROC DELETE DATA = XYZ; RUN;

%END;

PROC DELETE DATA = X&I; RUN;

%END;

%MEND IMPORTYR;

%MACRO TXT_TABTXT3 (IN_LOC,MN,MX,OUT_SAS);

%LET MNWK = %SUBSTR(&MN,5,2);

%LET MXWK = %SUBSTR(&MX,5,2);

%LET MNYR = %SUBSTR(&MN,1,4);

%LET MXYR = %SUBSTR(&MX,1,4);

%DO Y = &MNYR %TO &MXYR;

%IF &Y = 2006 %THEN %LET MAXWKX = 53;

%ELSE %LET MAXWKX = 52;

%IF &MXWK < &MNWK %THEN %DO;

%IF &MXYR NE &Y %THEN %DO;

%LET TMPMXWK = &MAXWKX;

%IF &MNYR = &Y %THEN %LET TMPMNWK = &MNWK;

%ELSE %LET TMPMNWK = 1;

%END;

%ELSE %DO;

%LET TMPMNWK = 1;

%LET TMPMXWK = &MXWK;

%END;

%ELSE %DO;

%IF &Y NE &MNYR %THEN %DO;

%LET TMPMNWK = 1;

%IF &Y NE &MXYR %THEN %LET TMPMXWK = &MAXWKX;

%ELSE %LET TMPMXWK = &MXWK;

%END;

%ELSE %DO;

%LET TMPMNWK = &MNWK;

%IF &Y = &MXYR %THEN %LET TMPMXWK = &MXWK;

%ELSE %LET TMPMXWK = &MAXWKX;

%END;

%PUT &Y &TMPMNWK &TMPMXWK;

%IMPORTYR (&IN_LOC,&TMPMNWK,&TMPMXWK,&Y);

%IF &MNYR = &Y %THEN %DO;

DATA &OUT_SAS;

SET TMP&Y;

RUN;

%END;

%ELSE %DO;

PROC APPEND FORCE

BASE = &OUT_SAS

DATA = TMP&Y;

RUN;

%END;

PROC DELETE DATA = TMP&Y; RUN;

%END;

%MEND TXT_TABTXT3;

SAS Truncating on PROC Import

art297 — Thu, 01 Sep 2011 15:20:21 GMT

I'm not sure where your problem is originating. If it is during the proc import calls you might be able to correct it by adding one line to each of your proc imports, namely: GUESSINGROWS=32767;

SAS Truncating on PROC Import

1800bigk — Thu, 01 Sep 2011 15:27:06 GMT

I looked at the log to see what SAS is doing and the problem is during the import.

format channel $4.

format product $3.

format sub_product $7.

SAS is saying my channel data is only 4 digits long but really there are other channels in my data that have more than 4 digits it just happens the first instance of channel is only 4 digits so it just cuts everything off after that.

SAS Truncating on PROC Import

art297 — Thu, 01 Sep 2011 15:35:13 GMT

Then I would definitely recommend including the guessingrows statements. If you don't include the statement, then proc import will only look at the first 20 records to determine formats and informats.

SAS Truncating on PROC Import

1800bigk — Thu, 01 Sep 2011 15:54:44 GMT

You my friend are a genius, that did it. Thanks so much.

topic SAS Truncating on PROC Import in SAS Procedures

SAS Truncating on PROC Import

SAS Truncating on PROC Import

SAS Truncating on PROC Import

SAS Truncating on PROC Import

SAS Truncating on PROC Import