Blank entries [proc append]

[mjheever]

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: 

[LinusH]

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.

[mjheever]

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 .

[Barnipaz]

data all_csv;

  infile '...cbs*.csv'

  put

    var1

   .

   .

   varn

  ;

run;

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

[LinusH]

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.

[Barnipaz]

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.

[ballardw]

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.

[Barnipaz]

Try with just one datastep.

data alldata;

set

  cbs01

  cbs02

.

.

.

  cbs26

;

run;

With a nmacro can be even easier

[mjheever]

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 .

[ballardw]

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.

[data_null__]

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

2790     observation details

[mjheever]

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: