topic Re: Hard coding in proc sql in SAS Programming

Hard coding in proc sql

Babloo — Fri, 05 Jan 2018 15:04:01 GMT

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

data test;
CURRENCY_CD ='EUR';
run;

Re: Hard coding in proc sql

PaigeMiller — Fri, 05 Jan 2018 15:09:15 GMT

@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.

Re: Hard coding in proc sql

LinusH — Fri, 05 Jan 2018 15:33:10 GMT

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.

Re: Hard coding in proc sql

art297 — Fri, 05 Jan 2018 15:37:32 GMT

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#n1ncn0pznd8wrln1tnp3xdxjz9xz.htm

e.g.:

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

Art, CEO, AnalystFinder.com

Re: Hard coding in proc sql

Babloo — Fri, 05 Jan 2018 18:39:43 GMT

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.

Re: Hard coding in proc sql

art297 — Fri, 05 Jan 2018 19:14:16 GMT

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

Re: Hard coding in proc sql

ballardw — Fri, 05 Jan 2018 20:06:35 GMT

@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.

Re: Hard coding in proc sql

Babloo — Sat, 06 Jan 2018 01:21:43 GMT

I'm trying to add that variable to an existing dataset.

Re: Hard coding in proc sql

Tom — Sat, 06 Jan 2018 01:23:27 GMT

@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;

Re: Hard coding in proc sql

art297 — Sat, 06 Jan 2018 01:46:25 GMT

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