Help using Base SAS procedures

Blank entries [proc append]

Reply
Contributor
Posts: 42

Blank entries [proc append]

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: 

Super User
Posts: 5,424

Re: Blank entries [proc append]

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.

Data never sleeps
Contributor
Posts: 42

Re: Blank entries [proc append]

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 Smiley Wink .

Contributor
Posts: 33

Re: Blank entries [proc append]

data all_csv;

  infile '...cbs*.csv'

  put

    var1

   .

   .

   varn

  ;

run;

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

Super User
Posts: 5,424

Re: Blank entries [proc append]

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.

Data never sleeps
Contributor
Posts: 33

Re: Blank entries [proc append]

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 Smiley Wink

It was a starting point for writing the code.

Super User
Posts: 11,343

Re: Blank entries [proc append]

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.

Contributor
Posts: 33

Re: Blank entries [proc append]

Try with just one datastep.

data alldata;

set

  cbs01

  cbs02

.

.

.

  cbs26

;

run;

With a nmacro can be even easier

Contributor
Posts: 42

Re: Blank entries [proc append]

Thank you for all the replies and help Smiley Happy.

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 .

Super User
Posts: 11,343

Re: Blank entries [proc append]

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.

Respected Advisor
Posts: 3,799

Re: Blank entries [proc append]

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

2790     observation details

Contributor
Posts: 42

Re: Blank entries [proc append]

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 Smiley Wink.

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

Ask a Question
Discussion stats
  • 11 replies
  • 1108 views
  • 0 likes
  • 5 in conversation