Hi, I have a dataset as follows:
variable level value
-----------------------
Age_group 1 0.1
Age_group 2 0.3
Age_group 3 0.2
Age_group 4 0.5
I would like to reformat it to get,
variable value
-------------------
(^\b Age_group)
1 0.1
2 0.3
3 0.2
4 0.5
Is there any way to perform this?
If you want to omit "variable" and rename "level" into 'variable", you can do it in the same step:
data want(rename=(level=variable));
set have(drop=variable);
run;
You can concatenate variables using cat function:
data want;
set have(drop=variable);
new_var = cat(strip(level), ' ',put(value, 3.1));
run;
A sas dataset is made of columns and rows (onservations).
Each observation holds all and same columns.
Do you want it as a report? something like:
data _null_;
set have;
by variable;
if first.variable then put @1 variable ;
put @3 level @10 value;
run;
Do you want a report or a dataset?
Your requirement is now clear and can be done by:
data want(keeo=new_var);
set have;
by variable;
length new_var $40;
if first.variable then new_var = variable ; else
new_var = left(level) || ' ' || put(value, 3.1);
run;
In case of any issue please post the log.
To have one observation become two you need to code explicit output statements.
Here is skeleton.
data want;
set have;
if _n_=1 then do;
....
output;
end;
....
output;
run;
Replace the dots with the logic you need to generate the values you want for those output observations.
Or you could read the first observation twice.
data want;
set sashelp.class(obs=1 keep=name rename=(name=new_var))
sashelp.class(in=in2 keep=name age height)
;
if in2 then new_var=cats(age);
drop name age;
rename new_var=Age;
run;
Results:
Obs Age Height 1 Alfred . 2 14 69.0 3 13 56.5 4 13 65.3 5 14 62.8 6 14 63.5
data have;
input variable $9. level value;
cards;
Age_group 1 0.1
Age_group 2 0.3
Age_group 3 0.2
Age_group 4 0.5
;
proc report;
columns variable level value;
define variable / group noprint;
define level /display 'Variable';
define value /display;
Compute before variable;
length Text $10;
if variable="Age_group" then
text="Age_group";
Line @1 Text $50.;
endcomp;
run;
Alternately, you could try creating a separate data set for the first observation. Then append/concatenate the original data set to the data set with one observation. In the below example, I'm assuming the original data set is data set two.
Data One;
Variable='(^\b Age_group)';
Value=.;
Output;
Run;
Data two;
Set two (Drop=Variable);
Rename Level=Variable;
Run;
Proc Append Base=One Data=Two;
Run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.