topic Re: new to SAS in SAS Procedures

new to SAS

deleted_user — Thu, 02 Sep 2010 10:10:50 GMT

I am new to SAS.

What I want to do is to read in an xls file, then either update the existing xls file or create a new xls file. But it seems only created a new xls file, didn't update the records.

Here is my code:

filename input "replace.xls";
filename output "replaceout.xls";

data testdata;
infile input recfm=f ignoreDOSeof;
input ;
RegularExperssionId=prxparse('s/M[. ]/MA/');
call prxchange(RegularExperssionId, -1, input);
file output recfm=f;
put _infile_;
run;

Thanks for all your help. And do let me know if I post the message in the wrong group.

Re: new to SAS

ArtC — Thu, 02 Sep 2010 19:04:19 GMT

The special variable _INFILE_ only contains the information from the input buffer. Although the information from the input buffer is written to the Program Data Vector, PDV, _INFILE_ does not receive any information from the program data vector. Modified variable values are contained in the PDV not in _INFILE_ as is shown here:
[pre}data a;
input x z$;
put _infile_;
put x=;
x = x+1;
put _infile_;
put x=;
datalines;
1 a
5 b
run;[/pre]

You need to write the values directly from the PDV not the input buffer.

Re: new to SAS

dhana — Fri, 03 Sep 2010 05:04:38 GMT

You can use PROCs to read and write to excel files. I have included an example code here for your reference.

PROC IMPORT OUT= WORK.excelout
DATAFILE= "D:\Finance\Analysis\Historydata.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="RegDta$";
GETNAMES=YES;
RUN;

The first line tells the dataset to be created.The second lline tells the location of the excel file to be read.The third line tells SAS the type of DBMS, in this case it is excel file.The fourth line tells which work sheet needs to be selected from the excel file. First line of the work sheet will be used as variable names, this is mentioned in the fifth line.

Like this you can PROC EXPORT to export your SAS dataset to an excel file.

PROC EXPORT DATA= WORK.ESTIMATES
OUTFILE= "C:\Documents and Settings\carreir\Desktop\SASestimates.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

Thanks

Dhanasekaran R
Allianz Cornhill Information Services
Trivandrum