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;
%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;
%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;
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;
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.
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.
You my friend are a genius, that did it. Thanks so much.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: