05-18-2014 02:24 AM
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 >
thanks for my help.
05-19-2014 03:30 AM
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.
Sorry, I can't see it. Perhaps just post some sample in thread would be sufficient?
05-19-2014 04:28 AM
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.
05-19-2014 11:56 AM
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=VAR; *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;
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';
Hope this is useful, cheers, Ragi