Yes, let's give it a try:

1. Write a program that exports all sheets to a directory. I do this by putting this in my main program:

filename workbook "C:\Work_Activities\Production_Source\SQL_Server_Raw_Data\injection_study.xlsx"; filename txtout "C:\Work_Activities\Production_Source\SQL_Server_Raw_Data\injection_study_files"; %include 'C:\Work_Activities\Production_Source\SAS_Programs\save_as_tab_delimited.sas'; options noxwait;

2. And then my file that contains save_as_tab_delimited contains this (this was a program written by another SAS-L contributor called data _null_, and I just use it as an include like above whenever I want to save as tab delimited:

/* filename workbook "C:\Work_Activities\testfiles\test.xlsx"; filename txtout "C:\Work_Activities\testfiles\"; */

data _null_ / pgm=work.sheets2txt; /* gather info */ length workbook txtout script filevar command $256; workbook = pathname('WORKBOOK'); txtout = pathname('TXTOUT'); script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs'); filevar = script; /* write the script */ file dummy1 filevar=filevar; put 'Const ' workbook=$quote256.; put 'Const ' txtout=$quote256.; put 'Set objExcel = CreateObject("Excel.Application")'; put +3 'With objExcel'; put +3 '.Visible = True'; put +3 '.DisplayAlerts = False'; put +3 'Set objWorkbook = .Workbooks.Open(workbook)'; put +3 'i = 0'; put +3 'Set colSheets = .WorkSheets'; put +3 'For Each objSheet In colSheets'; put +6 'i = i + 1'; put +6 'WScript.echo i & " " & objsheet.name '; put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".txt",21'; put +6 'Next'; put +3 '.Application.Quit'; put +3 'End With'; /* close the script file by opening another, not used */ filevar = catx('\',pathname('WORK'),'DUMMY.vbs');; file dummy1 filevar=filevar; /* look at the script, not necessary but may be useful */ infile dummy2 filevar=script end=eof; do until(eof); input; putlog _infile_; end; /* call the script */ command = catx(' ','cscript //NoLogo',quote(strip(script))); infile dummy3 pipe filevar=command end=eof; do until(eof); input; putlog _infile_; end; stop; run;

data pgm=work.sheets2txt; run; filename workbook clear; filename txtout clear;

Now then, you should have all of your sheets now saved into one directory; run this multiple times if you have multiple excel workbooks.

3. Then, setup your base file like I discussed:

data base; informat STUDYID $9. DOMAIN $8.; label studyid='Study Identifier' domain='Domain Abbreviation'; stop; run;

4. Then, you'd want to import each file, and if it has got records attach it to your base file

options mlogic symbolgen; %macro read; data set1_all; stop; run; filename exdir "C:\Work_Activities\Production_Source\SQL_Server_Raw_Data\injection_study_files"; %let dirid = %sysfunc(DOPEN(exdir)); %let dircnt = %sysfunc(DNUM(&dirid)); %do i = 1 %to &dircnt; %let dirread = %sysfunc(DREAD(&dirid,&i)); %put dirread = &dirread; data temp; informat dummy 1.; stop; run;

PROC IMPORT OUT= WORK.TEMP

DATAFILE= "C:\Work_Activities\Production_Source\SQL_Server_R

aw_Data\injection_study_files\&dirread"

DBMS=TAB REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

proc sql noprint; select count(*) as count into :count from temp; quit;

%if &count > 0 %then %do; %Let datasetname = %scan(&dirread,1,.); data temp; informat datasetname $50.; set temp; datasetname="&datasetname"; run;

data base; set base temp; run;

%end; %end; %let rc = %sysfunc(DCLOSE(&dirid)); %put rc = &rc; %mend read; %read;

-Mary

----- Original Message ----- From: sri To: SAS-L@LISTSERV.UGA.EDU Sent: Thursday, September 18, 2008 4:53 PM Subject: Re: Can some one help me to develop a macro to do this job

Thank you very much for your reply. we can do like this but is there any way that I can get the info directly in excel sheet and develop a data and can set or append the subsequent data. One more thing I for got to mention is that suppose if the variable RACECT is there then data for that variable should not have other than

1 = CAUCASIAN 2 = NON-CAUCASIAN Is there any way I can put the constraint like this

Since I have so much of data and so many different excel sheets with so many specifications every time creating a data is becoming difficult. If I can have a macro to import directly from excel sheet and create a data with the specs given will reduce lot of my work.

label studyid='Study Identifier' domain='Domain Abbreviation'; stop; run;

data newdata; infile cards; input studyid $ newvar $; cards; 001 xxx 002 yyy ; run;

data test; set test newdata; run;

----- Original Message ----- From: sri To: SAS-L@LISTSERV.UGA.EDU Sent: Thursday, September 18, 2008 3:07 PM Subject: Re: Can some one help me to develop a macro to do this job

Hi, Excel sheet has the following info. If the data set that is given has same variable then I should get the same label, type and length into new data that I will be deriving data. If any of the info is absent then I should get the info from original data itself. Can you please help me in doing this.

Name Label type length STUDYID Study Identifier Char 9 DOMAIN Domain Abbreviation Char 8 USUBJID Unique Subject Identifier Char 15 AESEQ Sequence Number Num 8 AEGRPID Group ID Char 100 AEREFID Reference ID Char 40 AESPID Sponsor-Defined Identifier Char 8 AETERM Reported Term for the Adverse Event char 200 AEMODIFY Modified Reported Term Char 200 AEDECOD Dictionary-Derived Term Char 200 AECAT Category for Adverse Event Char 100 AESCAT Subcategory for Adverse Event Char 100 AEOCCUR Adverse Event Occurrence Char AEBODSYS Body System or Organ Class char 100 AELOC Location of the Reaction Char 100

-- Srikrushna.k