topic Re: access SQL through data. in New SAS User

access SQL through data.

Abdulla1 — Thu, 03 Feb 2022 20:40:57 GMT

I've created a table on proc sql then tried to access it and adjust it through sas. it keeps giving me an empty output

I've used the following code:

proc sql;
create table mylib.test as
select *
from lib1.data1
where var2='202220'
order by var3, var4;

quit;

data temp.test;
rename var1=ID;
run;

Re: access SQL through data.

Reeza — Thu, 03 Feb 2022 20:48:38 GMT

The code is correct, syntax wise, but logically incorrect.

What are you trying to do?

The last data step will not do anything for example.

The code snippets don't seem to align, you create a table in mylib but then try and reference a data set in temp with the same name? However no input data set is provided.

I can only offer a very broad suggestion to fix it because I don't know what you're trying to accomplish.

data test;
set mylib.test;

rename var1 = ID;
run;

Re: access SQL through data.

Abdulla1 — Thu, 03 Feb 2022 21:34:56 GMT

Thanks that works very well.

however, I'm trying to change the variable var1 into ID can I do that without setting new variable

I tried the code below but it didn't work either.

proc sql;
create table mylib.test as
select *
from lib1.data1
where var2='202220'
order by var3, var4;

quit;

data mylib.test;
rename var1=ID;
run;

Re: access SQL through data.

Reeza — Thu, 03 Feb 2022 21:42:39 GMT

You have no SET statement.

A SET statement tells SAS what the input data set is. Since there is none, there is no data to work with in that step.

The data step actually deletes your initial step and replaces that data with nothing.

This is probably what you want but not a good idea. Whenever you have a data step its not a good idea to have your SET and DATA reference the same data set. This destroys your original data set so if you make a mistake you have to go back extra steps.

data mylib.test;
set mylib.test;

rename var1 = ID;
run;

Instead you can build on it:

data mylib.test2;
set mylib.test;

rename var1 = ID;
run;

Or use PROC DATASETS to rename the variable. This is actually the fastest, most efficient option as it just changes the metadata/variable name. In a Data step or SQL proc it actually go through all the data and recreates a new data set at the end.

proc datasets lib=mylib;
modify test;
rename var1=id;
run;quit;

@Abdulla1 wrote:

Thanks that works very well.

however, I'm trying to change the variable var1 into ID can I do that without setting new variable

I tried the code below but it didn't work either.

proc sql;
create table mylib.test as
select *
from lib1.data1
where var2='202220'
order by var3, var4;

quit;

data mylib.test;
rename var1=ID;
run;

Re: access SQL through data.

Abdulla1 — Thu, 03 Feb 2022 22:17:13 GMT

thank you @Reeza

that is very helpful.