Learning SAS? Welcome to the exclusive online community for all SAS learners.

sas data management

Posts: 69

sas data management

Hi everyone

i am new sas user. i have two types of data one is related to company and other related to banks. when i import these two excel data files into the sas i see some of the empty data files which are not the part of my data set??? hide all the extra columns and rows in the excel but still i am getting these files.

how to get rid of these empty columns??
* there are more than 15 variables and 10 countries. when i run a sas code for calculating the descriptive statistics i can found the so many tables one table for one set for each variables , which has further different tables for each country. i want a single table for all the variables and countries. is there any way i can get them ???

please share some codes to do so >

I have also attached my data file which has two sheets. one for bank specific variables and other for country specific variables. kindly also share some codes to marge these two data sets to make a one file so that i can run the regression and correlations and other statistical test.

thanks for my help.

Super User
Posts: 5,876

Re: sas data management

Posted in reply to raqthesolid

How to get rid of these empty columns?

Hiding columns in Excel does not help. You need to mark the columns and explicitly delete them (not just the contents).

I want a single table for all the variables and countries. is there any way i can get them ?

Yes, you can use i.e. proc append to put hem together. Be aware that you need to sync the files from each country to have the same set of columns names. Or create an empty template data set containing all variables/columns you wish to keep, than append to that.

I have also attached my data file...

Sorry, I can't see it. Perhaps just post some sample in thread would be sufficient?

Data never sleeps
Super User
Super User
Posts: 9,599

Re: sas data management

Just to add onto this, if you really *have* to use Excel for anything, you can try several techniques for improving data through put.  The obvious one is to convert the data into a text delimited file, you then have some more control.  You can also modify the Excel file itself, convert data to tables for instance and use libname excel to refer to named ranges etc.  At the end of the day what you are facing is the fact that Excel is not a good tool for data entry, the above example shows how Excel is hiding something from you.

New Contributor
Posts: 2

Re: sas data management

Posted in reply to raqthesolid

LIBNAME data1 excel 'C:\Users\vrr57108\Desktop\AexcelBook1.xls' ver=2000

dbSasLabel=none mixed=yes stringDates=yes scanTime=yes dbMax_text=32767;    *This will import the desired excel file;

/* code to drop unnecessary columns and append all the sheets in a workbook and manipulate the output data */

DATA profile (drop=VARSmiley Happy;    *the drop statement here will delete all the columns with name starting with VAR;

SET data1.'GFP$'n (in=a)   *this will be the sheet name of the whole excel workbook u have imported;

    data1.'IM$'n (in=b)     

    data2.'ALG_ENT$'n (in=c)

    data2.'NP_PA$'n (in=d)

    data2.'OTH$'n (in=e)

    data2.'PED$'n (in=f);

if a then field1='GFP';

        else if b then field1='IM';

            else if c then field1='ALG_ENT';

        else if d then field1='NP_PA';

    else if e then field1='OTH';

else cons_spec='PED';


Hope this is useful, cheers, Ragi

Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation