Hi,
I have dataset that looks like:
refper company placeholder_r0010 placeholder_r0020 placeholder_r0030 placeholder_r0040
201744 70912 . 71.889.742,2000000000 . 539.285,3600000000 26.431.317.719,6000000000 1.846.511.611,3300000000
201841 70912 . 85.787.103,2700000000 . 539.285,3600000000 26.066.828.054,7500000000 1.751.692.394,5900000000
201842 70912 . -43.904.340,3200000000 . 539.285,3600000000 26.937.620.831,9400000000 2.043.105.368,9100000000
201843 70912 . . . 537.775,2200000000 27.011.686.026,8700000000 1.215.625.799,2800000000
and another dataset which combines the placeholder column names with their actual names:
Goodwill R0010
Deferred acquisition costs R0020
Intangible assets R0030
Deferred tax assets R0040
How do I combine these two sets such that the column names in the first dataset changes to names given in the second data set? (such that: placeholder_r0010 == Goodwill, placeholder_r0020 == Deferred acquisition costs, ...)
Transpose and format, with usable example data:
data ds1;
input refper $ company $ (placeholder_r0010 placeholder_r0020 placeholder_r0030 placeholder_r0040) (:commax32.);
datalines;
201744 70912 . 71.889.742,2000000000 . 539.285,3600000000
201841 70912 . 85.787.103,2700000000 . 539.285,3600000000
201842 70912 . -43.904.340,3200000000 . 539.285,3600000000
201843 70912 . . . 537.775,2200000000
;
run;
data ds2;
infile datalines dlm=',';
input label :$32. start $;
fmtname = 'mylabel';
type = 'C';
datalines;
Goodwill,R0010
Deferred acquisition costs,R0020
Intangible assets,R0030
Deferred tax assets,R0040
;
run;
proc format cntlin=ds2;
run;
proc transpose
data=ds1
out=trans (
rename=(_name_=type col1=value)
)
;
by refper company;
var placeholder:;
run;
data want;
set trans;
type = upcase(scan(type,2,'_'));
format type $mylabel.;
run;
Ignore everything to the right of "placeholder_r0040". The website is not working with me.
@Thorius_Prime wrote:
The website is not working with me.
Because you have tabs in something that should be code. This is a VERY BAD IDEA.
Create a data step with datalines, and post that.
I would ask why your in this situation in the first place, i.e. having placeholders and names in another dataset. Its likely a bad import, or export at some part of the process and fixing it there would be more beneficial. Anyways, something like - and please post test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
In future!
proc sql; create table inter as select cats(a.name,"=",tranwrd(b.dsname," ","_")) as ren from (select * from sashelp.vcolumns where libname="<yourlib>" and memname="<yourds>") a left join ds b on scan(a.name,"_",2)=b.col2; quit; data _null_; set inter end=last;; if _n_=1 then call execute('data want; set have (rename=('); call execute(ren); if last. then call execute('));run;'); run;
What this does (and no test data so you will need to update for your data!!) is to create a rename list of variable name to text in second table - remembering that variable names cannot have spaces so these are replaced with _. This list of renames is then used in a generated datastep from the data _null_; You could (and probably should, use proc datasets rather than datastep).
Create a format from the second dataset.
Transpose the first dataset to a usable (long) format.
Remove the "placeholder_" from the _name_ column, and apply the format to it.
Note that the "names" from your second dataset can't be used in SAS.
Hello,
Spaces in variables names are not a goodf idea. The following program used the provided names
with spaces replaced by underscores.
data have;
length refper company placeholder_r0010 placeholder_r0020 placeholder_r0030 placeholder_r0040 3.;
call missing(of _ALL_);
run;
data placeholders;
infile cards dlm="," dsd;
length name $30.;
input name $ code $;
cards;
Goodwill , R0010
Deferred acquisition costs , R0020
Intangible assets , R0030
Deferred tax assets , R0040
;
run;
data _NULL_;
set placeholders end=eof;
if _N_=1 then call execute('data want; set have;');
call execute(cats('rename placeholder_',code,'=',tranwrd(strip(name)," ","_"),';'));
if eof then call execute('run;');
run;
Transpose and format, with usable example data:
data ds1;
input refper $ company $ (placeholder_r0010 placeholder_r0020 placeholder_r0030 placeholder_r0040) (:commax32.);
datalines;
201744 70912 . 71.889.742,2000000000 . 539.285,3600000000
201841 70912 . 85.787.103,2700000000 . 539.285,3600000000
201842 70912 . -43.904.340,3200000000 . 539.285,3600000000
201843 70912 . . . 537.775,2200000000
;
run;
data ds2;
infile datalines dlm=',';
input label :$32. start $;
fmtname = 'mylabel';
type = 'C';
datalines;
Goodwill,R0010
Deferred acquisition costs,R0020
Intangible assets,R0030
Deferred tax assets,R0040
;
run;
proc format cntlin=ds2;
run;
proc transpose
data=ds1
out=trans (
rename=(_name_=type col1=value)
)
;
by refper company;
var placeholder:;
run;
data want;
set trans;
type = upcase(scan(type,2,'_'));
format type $mylabel.;
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.