topic Re: Clibbing variables in Text and Data in Excel in SAS Procedures

Clibbing variables in Text and Data in Excel

sas_ — Wed, 17 Mar 2010 10:18:37 GMT

Hi
i am having variables in Text file with pipe delimited and the data for this is in another excel sheet how can i create a dataset out of this. In Base SAS,.

Re: Clibbing variables in Text and Data in Excel

sbb — Wed, 17 Mar 2010 10:45:01 GMT

Investigate using PROC IMPORT.

Scott Barry
SBBWorks, Inc.

Recommended Google advanced search, this topic/post:

proc import pipe delimited site:sas.com

Re: Clibbing variables in Text and Data in Excel

Patrick — Wed, 17 Mar 2010 11:53:22 GMT

If you're using SAS EG:
The import wizard (File/Import) makes reading data into SAS quite an easy task.

Re: Clibbing variables in Text and Data in Excel

sas_ — Wed, 17 Mar 2010 12:25:35 GMT

But i want in base sas

Re: Clibbing variables in Text and Data in Excel

sbb — Wed, 17 Mar 2010 13:25:14 GMT

You will need to use a DATA step approach. Refer to the SAS support http://support.sas.com/ website and the recommended Google advanced search argument for related technical and conference papers on the topic. Or use the website's SEARCH facility.

Scott Barry
SBBWorks, Inc.

Re: Clibbing variables in Text and Data in Excel

sas_ — Tue, 23 Mar 2010 11:29:13 GMT

i got the solution

/*First Read the variables in Excel sheet*/
PROC IMPORT OUT= WORK.var
DATAFILE= "C:\Documents and Settings\Desktop\var.xlsx"
DBMS=EXCEL REPLACE;
RANGE="sam$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

/*caputring all the variables in to macro varaiable*/
proc sql noprint;
select name into :name separated by " "
from dictionary.columns where libname="WORK" and memname="VAR";
quit;
/*datafile with obs*/
filename text "C:\Documents and Settings\Desktop\values.txt";
/*outputfile*/
filename output "C:\Documents and Settings\Desktop\Output.txt";

data _null_;
infile text;
file output;
if _N_ eq 1 then put "&name"; * put header row;
input; * read one row;
put _infile_; * write read buffer to second file;
run;

proc import out=final datafile="C:\Documents and Settings\Desktop\Output.txt" replace;
delimiter=" ";
getnames=yes;
run;

then import the output.txt in to new Dataset