topic Re: get the number from the column name, like symget in SAS Programming

get the number from the column name, like symget

blueskyxyz — Thu, 19 Mar 2020 09:55:35 GMT

to calculate the percent from the tables as below,

pct1=co1/n1;

pct2=col2/n2;

pct3.........

ord	col1	col2	col3	col4
1	10	20	53	32
2	7	15	30	40
3	10	20	31	43
4	15	25	30	40
5	19	40	23	50

n1-n5 are macro variables: &n1,,,&n4

total
n1	100
n2	200
n3	300
n4	400

data have;
input ord col1 col2 col3 col4 ;
cards;
1 5 10 6 30
2 17 15 7 25
3 20 25 8 40
;
run;


%let n1=100;
%let n2=200;
%let n3=100;
%let n4=200;

%macro pct();
data pct;
	set have;
	array a col1-col4;
	array b pct1-pct4;
	array c &n1- &n4 ;
	do i=1 to 4;
		b(i)=a(i)/c(i);
	end;
run;
%mend;
%pct;

the code doesn't work due to warning, don't use proc transpose.

/*is there a funciton */
data pct2;
	set have;
	pct{funtion(col(i)}=col{funtion(col(i)}/n{funtion(col(i)};
run;

Re: get the number from the column name, like symget

Kurt_Bremser — Thu, 19 Mar 2020 10:30:17 GMT

Why not use proc transpose, when it is the tool of choice?

Anyway, you need to correctly create an array with values:

data want1;
	set have;
	array a col1-col4;
	array b pct1-pct4;
	array c {4} _temporary_ (&n1 &n2 &n3 &n4);
	do i=1 to 4;
		b(i)=a(i)/c(i);
	end;
run;

using transpose:

data percentages;
input index n;
datalines;
1 100
2 200
3 100
4 200
;

proc transpose data=have out=trans;
by ord;
var col:;
run;

data trans2;
set trans;
if 0 then set percentages;
if _n_ = 1
then do;
  declare hash h (dataset:'percentages');
  h.definekey('index');
  h.definedata('n');
  h.definedone();
end;
index = input(compress(_name_,'','kd'),best.);
if h.find() = 0 then pct = col1 / n;
run;

proc transpose data=trans2 out=pct (drop=_name_) prefix=pct;
by ord;
var pct;
run;

data want2;
merge
  have
  pct
;
by ord;
run;

proc compare base=want1 compare=want2;
run;

The transpose method is completely data-driven and makes no assumptions about the number of elements anywhere in the code.

Re: get the number from the column name, like symget

FreelanceReinh — Thu, 19 Mar 2020 10:49:10 GMT

Hi @blueskyxyz,

Your idea to use SYMGET works as well:

data pct(drop=i);
set have;
array col[4];
array pct[4];
do i=1 to 4;
  pct[i]=col[i]/input(symget(cats('n',i)),32.);
end;
run;

Re: get the number from the column name, like symget

Astounding — Thu, 19 Mar 2020 15:35:20 GMT

Certainly using several functions, many times, on every observation will run up the bill. It would be wiser to put in the programming effort up front:


%macro getn;
   %local i;
   %do i=1 %to 4;
      &&n&i
   %end;
%mend getn;

With that work done, you could use this macro in your original program to replace one line:

array c {4} _temporary_ (%getn);

With that array in place, your program should work without any further changes.

Re: get the number from the column name, like symget

blueskyxyz — Tue, 31 Mar 2020 06:59:02 GMT

@Kurt_Bremser , thank you, sir.

Re: get the number from the column name, like symget

blueskyxyz — Tue, 31 Mar 2020 07:04:02 GMT

thanks all friends for this question, to use array, symget, transpose to get the percent.
sorry, the new version can only pick one as answer???