Wondered if there is an easy way to recode a variable, and keep the same name and position. For example, using the attached code, I can recode variable Type in sashelp.cars, after that I want to call it Type and keep the same position. Imagine there are hundreds of variables. I don't want to list all of them to keep the order. Is there any easy way to do it with too many coding?
PROC SQL;
CREATE TABLE want AS
SELECT Make,
Model,
case when Type in ("Hybrid", "Wagon")
then "Wagon"
else type
end as type,
Origin,
DriveTrain,
MSRP,
Invoice,
EngineSize,
Cylinders,
Horsepower,
MPG_City,
MPG_Highway,
Weight,
Wheelbase,
Length
FROM SASHELP.CARS;
QUIT;
Why does it have to have the same position? Please explain.
SAS doesn't care. All of your work will be done properly regardless of position.
No specific reason to keep the position. Our dataset is always arranged by different group of variables. Just wanted to keep the recoded var in the same group. like said wondered if there is an easy wat to do it without many additional codes.
@sasecn wrote:
No specific reason to keep the position. Our dataset is always arranged by different group of variables. Just wanted to keep the recoded var in the same group. like said wondered if there is an easy wat to do it without many additional codes.
Then why spend time on it at all?
@PaigeMiller wrote:
@sasecn wrote:
No specific reason to keep the position. Our dataset is always arranged by different group of variables. Just wanted to keep the recoded var in the same group. like said wondered if there is an easy wat to do it without many additional codes.
Then why spend time on it at all?
I don't know about you but I cannot keep the details of the contents of the 100's of thousands of individual datasets I might have to examine. If they are organized rationally then they are a h*** of lot easier to work with. You can open them and look at the first few records. Poke around. See what is going on.
For a vast majority of cases there is no reason to create a new data set for such a simple activity.
When displaying the values of one or more variables with the same value and desired display then often creating a custom format and applying that will do what you want. No new data set. Flexible as a change in the definition of that format is automatically applied at us.
Example:
proc format; value $alttype "Hybrid", "Wagon" = "Wagon" ; run; Proc freq data=sashelp.cars; table type; format type $alttype.; run;
The above format only affects the given values, none of the the other values of a variable would be affected.
Note that the group counts with that format applied combine the two types as one. Most of the graphing, reporting and analysis procedures will honor this behavior.
No need to create a new data set. See the example.
You could use Proc Datasets to change the default format of variables which avoids the time of rebuilding a data set, which can be significant with large sets.
Doing that is trivial if you use normal SAS code.
data want;
set sashelp.cars;
if Type in ("Hybrid", "Wagon") then type="Wagon";
run;
And if you actually had to change the type (or the length for character variables) it is not that much harder. You will need to know the name of the FIRST variable in the existing dataset.
data want;
set sahelp.cars(keep=make--type rename=(type=oldtype));
length type $100;
set sashelp.cars(drop=type);
if oldtype in ("Hybrid", "Wagon") then type="Wagon";
else type=oldtype;
drop oldtype;
run;
Thank you!
The order of variables in a tables shouldn't matter. This is more of an "Excel" thinking. But understand the convenience a defined order can provide.
If you want to create a totally new variable and have it added at a defined location in the target table then below some options.
For a data step (the two dashes in the variable list are important!):
data test_ds;
/* column mapping */
if 0 then
do;
set sashelp.class(keep=name--age);
length new_col $3;
set sashelp.class(keep=height--weight);
end;
call missing(of _all_);
/* read the source data */
set sashelp.class;
new_col='XXX';
run;
It's a bit more involved for a SQL as there the order of variables in the SELECT clause defines the order in the created table so you need to list all the variables.
To reduce typing if there are too many variables in the source table below some "lazy" way to get there.
1. Create list of all columns in source table
options ps=max ls=max;
proc sql feedback noexec;
select *
from sashelp.class as t
;
quit;
2. Copy/paste expanded SQL found in SAS Log into Notepad++ and run plugin "Poor Man's SQL Formatter"
In SAS log:
Result in Notepad++ after running the plugin
SELECT T.Name ,T.Sex ,T.Age ,T.Height ,T.Weight FROM SASHELP.CLASS T;
3. Copy/past the formatted code from Notepad++ back into your SAS Editor and add the new column at the desired location
proc sql;
create table test_sql as
SELECT T.Name
,T.Sex
,T.Age
,'XXX' as new_col length=3
,T.Height
,T.Weight
FROM SASHELP.CLASS T;
quit;
First, if you are using DATA step to recode variables, then variable order will be preserved.
data wnt;
set sashelp.cars;
if type='Hybrid' then type='Hyb'; else
if type='Sedan' then type='Sed'; else
if type='Sports' then type='Spo'; else
if type='Truck' then type='Trk';
run;
But if you MUST use proc sql, then
proc sql noprint;
create view vcars as select
case when type='Hybrid' then 'Hyb'
when type='Sedan' then 'Sed'
when type='Sports' then 'Spo'
when type='Truck' then 'Trk'
else type
end as type
,*
from sashelp.cars;
quit;
data want;
set sashelp.cars (obs=0)
vcars;
run;
The first idea here is that creating a view is just defining a set of rules. The data is not processed until the view is used. So you are not wasting resources writing an intermediate data set file to disk. The recoding doesn't take place until the subsequent DATA step.
The second idea is using the "obs=0" option for the first dataset named in the SET statement. This will read no observations directly from sashelp.cars but the second argument instructs SAS to read all the data from VCARS. Because the SAS compiler will construct the program data vector ordering variables as they are encountered, they will be ordered as in sashelp.cars, since it is the first argument of the SET statement.
Thanks for the details!
I tried your code (second code block) in sas enterprise guide 8.3 Update 3 (8.3.3.181) (64-bit) but it doesn't work, in the sense that the variable "type" of the table "want" is equal to the variable "type" of the table "sashelp.cars".
In fact if I run
proc compare
base=sashelp.cars
comp=want;
run;
the output is "No unequal values were found. All values compared are exactly equal."
The are no warnings in the log, but I noticed a strange thing which is depicted in the screenshot below: the output of the sql code creating the view contains both the "type" variables (the new one is at the beginning). Is this supposed to happen?
That really should not happen with a SAS dataset since there can only be one variable named TYPE (variable names are NOT case sensitive).
But apparently PROC SQL is allowing you to create a view with both a variable named type and one named Type. Kind of like how the running a SELECT statement (without the CREATE prefix) allows you to output values from multiple variables that have the same name.
And worse if you then try to use that view in a data step SAS will use the first one to set the TYPE and LENGTH but then retrieve data from both of them so that it tries to fit the values read from the second into the variable defined by the first one.
Try this example:
proc sql;
create table ds1 as
select 'X' as type,*
from sashelp.cars(keep=make type obs=5)
;
create view v1 as
select 'X' as type,*
from sashelp.cars(keep=make type obs=5)
;
quit;
data ds2; set v1; run;
proc print data=ds1; run;
proc print data=v1; run;
proc print data=ds2; run;
You can see warnings in the log:
1 proc sql; 2 create table ds1 as 3 select 'X' as type,* 4 from sashelp.cars(keep=make type obs=5) 5 ; WARNING: Variable Type already exists on file WORK.DS1. NOTE: Table WORK.DS1 created, with 5 rows and 2 columns. 6 create view v1 as 7 select 'X' as type,* 8 from sashelp.cars(keep=make type obs=5) 9 ; NOTE: SQL view WORK.V1 has been defined. 10 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 11 data ds2; set v1; run; WARNING: Multiple lengths were specified for the variable type by input data set(s). This can cause truncation of data. NOTE: There were 5 observations read from the data set SASHELP.CARS. NOTE: There were 5 observations read from the data set WORK.V1. NOTE: The data set WORK.DS2 has 5 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
That's interesting. I didn't realize a SQL view could have multiple columns with the same name.
proc sql;
create view v1 as
select 1 as Foo
,2 as Foo
,3 as FoO
,'4' as Foo
from sashelp.cars
;
quit;
proc contents data=v1 varnum;run ;
Returns:
Data Set Name WORK.V1 Observations . Member Type VIEW Variables 4 Engine SQLVIEW Indexes 0 Created 08/19/2024 12:01:53 Observation Length 25 Last Modified 08/19/2024 12:01:53 Deleted Observations 0 Protection Compressed NO Data Set Type Sorted NO Label Data Representation Default Encoding Default Variables in Creation Order # Variable Type Len Flags 1 Foo Num 8 P-- 2 Foo Num 8 P-- 3 FoO Num 8 P-- 4 Foo Char 1 P--
You can also use something like that to see how PROC SQL and DATA step handle variable attributes like LENGTH/FORMAT/INFORMAT and LABEL differently when confronted with multiple versions of the same variable. (Which can happen with SET or MERGE in SAS and UNION in SQL.)
proc sql;
create view v1 as
select '111' as Foo
,'2' as Foo label='Two'
,'3' as Foo format=$1.
,'4' as Foo informat=$1.
from sashelp.cars
;
create table ds1 as select * from v1;
quit;
data ds2; set v1; run;
proc contents data=ds1; run;
proc contents data=ds2; run;
SQL uses the first variables attributes.
Data step uses the first non-empty attribute.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: