topic Re: Blank entries [proc append] in SAS Procedures

Blank entries [proc append]

mjheever — Fri, 21 Feb 2014 08:12:11 GMT

Hi SAS Community!

I've imported 26 excel files into SAS and created a data set (or table) for each of them.

The goal is to create one table out of all these files (the variables are exactly the same).

I've used proc append in such a manner:

data alldata;

set cbs01;

run;

proc append base=alldata data=Cbs02 force;

run;

proc append base=alldata data=Cbs03 force;

run;

This seemed to have worked fine except that with two of the 26 files, say cbs02 and cbs03, some variable values are blank in the appended alldata set.

When I looked at the attributes of the variables and noticed that these two files's variables are of numeric format and the other files' variables are in char format.

I've tried to change the cell format in excel manually to text, but this does not seem to solve the problem.

Additionally I've tried to create separate data sets for these files and use the put function to change their formats but then the order of the variables are all messed up and can't append them to the rest of the datasets.

Any suggestions ?

Thank you :smileygrin: :smileygrin: :smileygrin:

Re: Blank entries [proc append]

LinusH — Fri, 21 Feb 2014 09:12:48 GMT

Lesson learned is to avoid Excel whenever possible.

If this is one time job, I suggest that you export the Excel-files as csv, and try to import them instead. That will hopefully get rid of some hard-to-understand Excel cell formatting which riddles SAS.

Re: Blank entries [proc append]

Barnipaz — Fri, 21 Feb 2014 09:22:24 GMT

Try with just one datastep.

data alldata;

set

cbs01

cbs02

cbs26

;

run;

With a nmacro can be even easier

Re: Blank entries [proc append]

mjheever — Fri, 21 Feb 2014 09:27:08 GMT

But that is the problem, all the files are in .csv format but still the problem remains.

What alternative would you recommend for Excel ?

Thanks for the reply .

Re: Blank entries [proc append]

Barnipaz — Fri, 21 Feb 2014 09:38:04 GMT

data all_csv;

infile '...cbs*.csv'

put

var1

varn

;

run;

In this way load all the csv file that have the parttern 'csb*.csv'

Re: Blank entries [proc append]

LinusH — Fri, 21 Feb 2014 10:46:56 GMT

Proc Append doesn't care about the ordring of variables (as it's close cousin insert into in SQL), so there's probably some other kind of problem. If this is a one time shot, changing the variable type in SAS would be my recommendation. Be sure after your put() that the resulting variable have the same type, length, format and label, and drop the original variable.

Be aware of if they initially have been imported as numeric, there could be char values in the original file which will be set to missing in SAS.

What method do you use for importing, PROC IMPORT or import wizard in Enterprise Guide, or...?

If you use PROC IMPORT, take a look at the GUESSINGROWS statement, could help you resolve the type mismatch during the import.

Re: Blank entries [proc append]

Barnipaz — Fri, 21 Feb 2014 10:54:02 GMT

May be using force option in the append it can work. We don't kwnow without the log (ILWT - In Log We Trust)

I know that in the datastep it should be inserted the length and the informat

It was a starting point for writing the code.

Re: Blank entries [proc append]

ballardw — Mon, 24 Feb 2014 22:41:10 GMT

Betting that you started with proc import which made guesses for each input file.

Use a a fileref to point to all of the CSV files:

filename csvin "c:\path to the data files\*.csv";

and then import the fileref CSVIN instead of the individual files. Set guessing rows large. This get all of the data ends up in one data set. HOWEVER you only want column headings to appear the first alphabetical named CSV file otherwise the column headings in the following data sets will make all of the variable character to accomodate the column headings.

Re: Blank entries [proc append]

mjheever — Tue, 25 Feb 2014 07:58:09 GMT

Thank you for all the replies and help .

It seems the problem occurs with the importing step, but I'm not sure why:smileyplain:...

Here is the Log info:

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary

parameter values will be saved to WORK.PARMS.PARMS.SLIST.

/********************************File info*******************************************************/

NOTE: The infile 'filepath' is:

Filename=filepath,

RECFM=V,LRECL=32767,File Size (bytes)=266031,

Last Modified=28 October 2013 14:09:32,

Create Time=13 November 2013 14:17:43

NOTE: Invalid data for RECIPIENT_ACCOUNT in line 2790 16-25.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---

2790 observation details

_ERROR_=1 _N_=2789

All the other files seem to be fine when they are imported.

I went to the specific entry but I can't find anything out of the ordinary .

Re: Blank entries [proc append]

ballardw — Tue, 25 Feb 2014 15:11:13 GMT

I'd make a small wager that the recipient_account is being created as numeric. If so, open the input file and find the row 2790. I'll bet you find a character value in that field. If it isn't obvious look for a zero that is actually a capital O or an imbedded space.

Since you haven't shared the code used to import the data I suspect you used proc import with the default of 20 for guessing rows, which only examines the first 20 rows of data to determine data type. Add guessingrows= 32767 to the proc import statement.

Re: Blank entries [proc append]

data_null__ — Tue, 25 Feb 2014 15:16:32 GMT

The answer will be found the the LOG output you omitted and labeled.

2790 observation details

Re: Blank entries [proc append]

mjheever — Fri, 28 Feb 2014 07:22:06 GMT

Hi guys!

The excel was corrupt it seems, since after deleting and re sending it the program worked just fine.

Thank you for all the input. I'm sure it will come in handy at some point in time .

Enjoy the weekend ! :smileygrin: :smileygrin: :smileygrin: