DATA Step, Macro, Functions and more

Hard coding in proc sql

Reply
Super Contributor
Posts: 625

Hard coding in proc sql

Appreciate if someone of you help me translate the below code in proc sql.

 

data test;
CURRENCY_CD ='EUR';
run;
Respected Advisor
Posts: 2,988

Re: Hard coding in proc sql


Babloo wrote:

Appreciate if someone of you help me translate the below code in proc sql.

 

data test;
CURRENCY_CD ='EUR';
run;

Surely you can take a try at doing this in SQL yourself. If you can't get it, show us your code and we'll be able to help.

--
Paige Miller
Super User
Posts: 5,876

Re: Hard coding in proc sql

SAS SQL is very close to/based on ANSI SQL. So you can practically take any SQL training and apply it to SAS.
That said, what so you want to do this at all? There could be some reasons to create a data set with just one value, but why do you need to be SQL?
That said, the SELECT statement requires a FROM clause so this kind of operations are easier dealt with using the data step.
Data never sleeps
PROC Star
Posts: 8,163

Re: Hard coding in proc sql

While it's easier to do with a datastep, you can do the same thing using proc sql. You can read about it at: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n1ncn0pznd8wrln1tn...

 

e.g.:

proc sql;
  create table test
    (CURRENCY_CD char(3))
  ;
  insert into test
    values('EUR')
  ;
quit;

Art, CEO, AnalystFinder.com

Super Contributor
Posts: 625

Re: Hard coding in proc sql

I'm doing a data manipulation in Proc sql and as part of that I need to
create the new variable as mentioned in the post. I'm trying to fit the
new variable in the Proc sql which I'm working on instead of creating the
new datastep for one variable.
PROC Star
Posts: 8,163

Re: Hard coding in proc sql

Are you trying to use proc sql to create a file containing just the one new variable with a constant value, or are you trying to add that variable to an existing dataset?

 

Art, CEO, AnalystFinder.com

 

Super Contributor
Posts: 625

Re: Hard coding in proc sql

I'm trying to add that variable to an existing dataset.
PROC Star
Posts: 8,163

Re: Hard coding in proc sql

If your dataset is called test you could use:

proc sql;
  alter table test
    add CURRENCY_CD char(3)
  ;
  update test
    set CURRENCY_CD='EUR'
  ;
quit;

Of course, it could be any other name, just change the two instances where my suggested code uses test.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 13,508

Re: Hard coding in proc sql


Babloo wrote:
I'm doing a data manipulation in Proc sql and as part of that I need to
create the new variable as mentioned in the post. I'm trying to fit the
new variable in the Proc sql which I'm working on instead of creating the
new datastep for one variable.

Hint: Show a brief example of what you have and a matching example of what the result should look like.

 

Your initial post did not create a data set or reference a data set so you left out a lot of details about what you might be attempting to do.

 

This code will create a new data set from an existing one and add  new variables with the same value for each record:

 

proc sql;

   create table new as

   select *, Newvar='Some text', SomeNum=3

   from sashelp.class;

quit;

 

If this does not match what you are attempting to do then please refer to the HINT.

 

Super User
Super User
Posts: 8,073

Re: Hard coding in proc sql


ballardw wrote:

... 

proc sql;

   create table new as

   select *, Newvar='Some text', SomeNum=3

   from sashelp.class;

quit;

 

... 


That won't work.  That is still assignment statement syntax instead of SELECT statement syntax.

proc sql;
create table new as
  select *, 'Some text' as NewVar, 3 as SomeNum
  from sashelp.class
;
quit;
Ask a Question
Discussion stats
  • 9 replies
  • 339 views
  • 6 likes
  • 6 in conversation