topic Re: Combing names from dataset with column names in other dataset in SAS Programming

Combing names from dataset with column names in other dataset

Thorius_Prime — Wed, 21 Nov 2018 12:46:08 GMT

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, ...)

Re: Combing names from dataset with column names in other dataset

Thorius_Prime — Wed, 21 Nov 2018 12:48:06 GMT

Ignore everything to the right of "placeholder_r0040". The website is not working with me.

Re: Combing names from dataset with column names in other dataset

RW9 — Wed, 21 Nov 2018 12:48:53 GMT

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-data-AKA-generate/ta-p/258712

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

Re: Combing names from dataset with column names in other dataset

Kurt_Bremser — Wed, 21 Nov 2018 12:56:34 GMT

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.

Re: Combing names from dataset with column names in other dataset

gamotte — Wed, 21 Nov 2018 12:57:37 GMT

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;

Re: Combing names from dataset with column names in other dataset

Kurt_Bremser — Wed, 21 Nov 2018 13:01:55 GMT

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

Re: Combing names from dataset with column names in other dataset

Kurt_Bremser — Wed, 21 Nov 2018 13:14:54 GMT

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;

Re: Combing names from dataset with column names in other dataset

Peter_C — Wed, 21 Nov 2018 22:49:45 GMT

Sounds like a candidate for a data _null_ step to call execute a proc datasets that will change column names and install those descriptive labels