DATA Step, Macro, Functions and more

how to rename the variable in proc sql

Reply
New Contributor
Posts: 3

how to rename the variable in proc sql

i need help to rename the below

 

 

%inc '\\sv-sasdw01p\FAD\Autoexec\fa_user_remote.sas';

LIBNAME SWORK SLIBREF=WORK SERVER=FASV;

proc import out= work.all

datafile="C:\Users\bhe\Desktop\Ricky.xlsx"

out=ricky

dbms=Excel replace;

sheet= "COMBINE_DATA$";

run;

endrsubmit;

 

proc sql;

create table ricky2 rename as

select

membno as Member ID

members_prmgrp as mbr_prmgrp

bthdat as Birthdate

Network_Name as Network

from Ricky;

quit;

endrsubmit;

 

 

Super User
Posts: 13,008

Re: how to rename the variable in proc sql

Posted in reply to beckyhe21

Rename what? The table? then use a different name on the create table.

 

Not valid syntax:

create table ricky2 rename as

create table must be followed by a valid data set name. "ricky2 rename" is not a valid name.

 

This doesn't work:

membno as Member ID

because Member Id is not a valid SAS variable name.

If you have an actual variable with name like that you are trying to match then what you want might be

membno as 'Member ID'n

 

 

 

By the way, relying on Proc Import (a guessing procedure) and Excel (an unstructured data source) together means that you will spend much time fixing things to combine data.

New Contributor
Posts: 3

Re: how to rename the variable in proc sql

[ Edited ]

 have to fix the Syntax error,

 

 

 

I want to name the table as ricky2  and i keep getting the sysntax error.

854 proc sql;

855 create table ricky2 rename as,

------

22

202

ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

856 select

857 File_source,

-----------

22

ERROR 22-322: Syntax error, expecting one of the following: ',', '.'.

 

 

 

 

 

 

rsubmit;

proc sql;

create table ricky2 rename as,

select

File_source,

memstat,

membno as Member_ID,

mbr_prmgrp as Member_prmgrp,

mbr_benpkg,

mbr_grpnum,

firstenr,

contenr,

Region,

bthdat as Birthdate,

mbr_pcpno,

mbr_pcpporg,

Hospital Name,

Network_Name as Network,

ratecd,

Last Name,

First Name,

Street,

madrln2,

City,

State,

zipcnty as Zip/Postal Code,

mwrkphn as Work Phone,

mhomphn as Phone,

emailadr as Email,

plstnam,

pfstnam,

parent,

padrln1,

padrln2,

pcitycd,

pstacod,

pzipcod,

pwrkphn,

prov_email,

Reporting_Level as rptlevel,

Mulesoft Exception,

Line of Business,

Household ID,

from Ricky

;quit;

endrsubmit;

Valued Guide
Posts: 557

Re: how to rename the variable in proc sql

Posted in reply to beckyhe21

If all you want to do is change the name of the data set and some of it's variables you should be looking at Proc Datasets and not Proc SQL - that's the sort of thing Proc Datasets was designed for and will be more efficient than SQL. Here's an example, you change the name of the data set with the CHANGE statement and the variables with RENAME

 

data class;
	set sashelp.class;
run;

proc datasets lib=work;
	change class=myclass;
	modify myclass;
	rename name=firstname;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 695 views
  • 2 likes
  • 3 in conversation