divide many columns by same value

didymo — Mon, 20 Sep 2021 17:48:42 GMT

Is there an easier way than using proc transpose to divide many columns by a common value, such as 0.1, drop the original columns, and use the new columns divided by 0.1 when the data is in this format below?

Site	Subsite	Type	Sample	Date	Year	Amphibia	Nematomorpha	Bivalvia	Gastropoda	Decapoda	Collembola	Ephemeroptera_Unknown	Ephemeroptera_Baeticidae	Ephemeroptera_Baetidae	Ephemeroptera_Caenidae	Ephemeroptera_Ephemerellidae	Ephemeroptera_Ephemeridae	Ephemeroptera_Heptageniidae	Ephemeroptera_Isonychiidae	Ephemeroptera_Leptophlebiidae	Odonata_Ashenidae	Odonata_Cordulegastridae	Odonata_Gomphidae	Plecoptera_Unknown	Plecoptera_Chloroperlidae	Plecoptera_Leuctridae	Plecoptera_Nemouridae	Plecoptera_Perlidae	Plecoptera_Perlodidae	Plecoptera_Peltoperlidae	Plecoptera_Pteronarcyidae	Plecoptera_Taeniopterygidae	Hempiptera_Veliidae	Megaloptera_Corydalidae	Tichoptera_Unknown	Tichoptera_Brachycentridae	Tichoptera_Glossosomatidae	Tichoptera_Hydropsychidae	Tichoptera_Lepidostomatidae	Tichoptera_Leptoceridae	Tichoptera_Limnephilidae	Tichoptera_Odontoceridae	Tichoptera_Philopotamidae	Tichoptera_Polycentrapodidae	Tichoptera_Rhyacophilidae	Tichoptera_Thremmatidae	Coleoptera_Unknown	Coleoptera_Anthicidae	Coleoptera_Curculionidae	Coleoptera_Dytiscidae	Coleoptera_Dryopidae	Coleoptera_Elmidae	Coleoptera_Hydrophilidae	Coleoptera_Psephenidae	Coleoptera_Ptilodactylidae	Diptera_Unknown	Diptera_Ceratopogonidae	Diptera_Chironomidae	Diptera_Dixidae	Diptera_Empididae	Diptera_Limoniidae	Diptera_Pediciidae	Diptera_Psychodidae	Diptera_Simuliidae	Diptera_Tabanidae	Diptera_Tipulidae
PinchGut	IndianCreek	Reference	1	5/18/20	2020	0	0	0	0	0	2	0	0	4	0	0	0	1	0	2	0	0	0	7	2	0	1	2	0	2	0	0	0	0	0	0	5	0	0	0	0	0	4	0	1	0	0	0	0	0	0	9	0	0	0	0	0	37	0	0	0	0	0	13	0	0
PinchGut	IndianCreek	Reference	2	5/18/20	2020	0	0	0	0	0	0	0	0	0	0	2	0	2	0	0	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	2	0	0	0	0	0	0	0	12	0	1	0	0	0	17	0	0	2	0	0	3	0	0
PinchGut	IndianCreek	Reference	3	5/18/20	2020	0	0	0	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	3	0	0	0	2	1	0	0	0	0	0	0	0	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	8	0	0	0	0	0	1	0	0
PinchGut	IndianCreek	Reference	4	5/18/20	2020	0	0	0	0	0	0	0	0	6	0	3	0	1	0	7	0	0	0	8	0	0	0	5	0	1	0	0	0	0	0	0	7	1	0	0	0	0	0	0	0	0	0	0	0	0	1	17	0	0	0	0	0	40	0	1	2	0	0	1	0	0
PinchGut	IndianCreek	Reference	5	5/18/20	2020	0	0	0	0	0	3	2	0	2	0	0	0	0	0	0	0	0	0	8	0	0	0	5	0	5	0	0	0	0	0	0	11	2	0	0	0	0	0	0	0	0	0	0	0	0	0	10	0	1	0	0	0	45	0	0	0	0	0	55	0	0
PinchGut	UT1_R4	Treatment	1	5/18/20	2020	0	0	0	0	0	0	6	0	0	0	0	0	5	0	8	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	37	1	0	4	0	1	2	0	0
PinchGut	UT1_R4	Treatment	2	5/18/20	2020	0	0	0	0	0	0	1	0	25	0	0	0	8	0	0	0	0	0	0	0	3	0	5	6	0	0	0	0	0	0	0	3	12	0	0	0	0	2	0	0	0	0	0	0	0	0	1	0	1	0	2	1	476	0	2	19	0	0	41	0	0
PinchGut	UT1_R4	Treatment	3	5/18/20	2020	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	2	39	0	0	1	0	0	1	0	0
PinchGut	UT1_R4	Treatment	4	5/18/20	2020	0	0	0	0	0	0	0	0	0	0	0	0	7	0	6	0	0	0	1	0	1	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	0	1	0	0	3	324	0	0	9	0	0	0	0	0
PinchGut	UT1_R4	Treatment	5	5/18/20	2020	0	0	0	0	0	0	1	0	32	0	0	0	3	0	0	0	0	0	2	0	0	0	4	1	0	0	0	0	0	1	0	2	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	5	742	0	0	26	0	0	75	0	0
PinchGut	Candiff_M3	Restored	1	5/18/20	2020	0	0	0	0	0	0	0	0	1	0	0	0	7	0	1	0	0	0	1	0	1	0	6	0	0	0	0	0	0	0	0	9	5	0	0	0	0	1	0	0	1	0	0	0	0	0	1	0	2	0	0	0	74	0	0	12	0	0	1	0	0
PinchGut	Candiff_M3	Restored	2	5/18/20	2020	0	0	0	0	0	3	4	0	3	0	1	0	4	6	0	0	0	0	10	0	0	2	31	7	1	0	0	1	0	1	0	31	104	0	0	0	0	5	0	0	0	0	0	0	0	0	3	0	0	0	0	0	956	1	0	9	0	0	12	0	0
PinchGut	Candiff_M3	Restored	3	5/18/20	2020	0	0	0	0	0	0	0	0	13	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	1	0	0	0	0	0	2	0	1	0	0	1	428	0	0	3	0	0	0	0	0

I used the following which works but was curious if there is a better way.

proc transpose data=step1a out=trans1; *(drop=_NAME_ _LABEL_);
var Amphibia Nematomorpha Bivalvia
Gastropoda Decapoda Collembola Ephemeroptera_Unknown Ephemeroptera_Baeticidae
Ephemeroptera_Baetidae Ephemeroptera_Caenidae Ephemeroptera_Ephemerellidae
Ephemeroptera_Ephemeridae Ephemeroptera_Heptageniidae Ephemeroptera_Isonychiidae
Ephemeroptera_Leptophlebiidae Odonata_Ashenidae Odonata_Cordulegastridae Odonata_Gomphidae
Plecoptera_Unknown Plecoptera_Chloroperlidae Plecoptera_Leuctridae Plecoptera_Nemouridae
Plecoptera_Perlidae Plecoptera_Perlodidae Plecoptera_Peltoperlidae Plecoptera_Pteronarcyidae
Plecoptera_Taeniopterygidae Hempiptera_Veliidae Megaloptera_Corydalidae Tichoptera_Unknown Tichoptera_Brachycentridae
Tichoptera_Glossosomatidae Tichoptera_Hydropsychidae Tichoptera_Lepidostomatidae Tichoptera_Leptoceridae Tichoptera_Limnephilidae
Tichoptera_Odontoceridae Tichoptera_Philopotamidae Tichoptera_Polycentrapodidae Tichoptera_Rhyacophilidae
Tichoptera_Thremmatidae Coleoptera_Unknown Coleoptera_Anthicidae Coleoptera_Curculionidae Coleoptera_Dytiscidae
Coleoptera_Dryopidae Coleoptera_Elmidae Coleoptera_Hydrophilidae Coleoptera_Psephenidae Coleoptera_Ptilodactylidae
Diptera_Unknown Diptera_Ceratopogonidae Diptera_Chironomidae Diptera_Dixidae Diptera_Empididae Diptera_Limoniidae
Diptera_Pediciidae Diptera_Psychodidae Diptera_Simuliidae Diptera_Tabanidae Diptera_Tipulidae pair;
by Site Subsite Type Sample Date Year Pair;
*proc print data=trans1;

data step1b;
set trans1;

density_m2=col1/0.09;
log_density_m2=log(density_m2+1); /* TRANSFORMATION ... */

if type="Referenc" then numberofstreams=3; else numberofstreams=5; /* WEIGHTING TO APPLY TO TYPE MEANS */
proc print data=step1b;

Re: divide many columns by same value

sbxkoenk — Tue, 21 Sep 2021 18:57:58 GMT

Hello,

Written "blind", so there may be errors :

/* query dictionary tables */  

PROC SQL noprint;
 select      name          into :OrigCols separated by ' '
 from dictionary.columns
 where libname='MYLIB' and memname='MY_DS_NAME'
       and upcase(name) NOT IN ('SITE','SUBSITE','TYPE','SAMPLE','DATE','YEAR','PAIR')
       and upcase(type)='NUM';
 select trim(name)!!'_NEW' into :NewCols  separated by ' '
 from dictionary.columns
 where libname='MYLIB' and memname='MY_DS_NAME'
       and upcase(name) NOT IN ('SITE','SUBSITE','TYPE','SAMPLE','DATE','YEAR','PAIR')
       and upcase(type)='NUM';
QUIT;
%PUT &=OrigCols;
%PUT &=NewCols;

data work.want(drop=i drop=&OrigCols.);
 set MYLIB.MY_DS_NAME;
 array origC{*} &OrigCols.;
 array  newC{*}  &NewCols.;
 do i=1 to dim(origC);
  newC(i)=origC(i)/0.1;
 end;
run;
/* end of program */

Koen

Re: divide many columns by same value

ChrisNZ — Tue, 21 Sep 2021 23:38:46 GMT

Another way:

proc sql;
  select catt(NAME,'=',NAME,'/0.1') into :divide separated by ';'
  from DICTIONARY.COLUMNS
  where LIBNAME      ='MYLIB' 
    and MEMNAME      ='MY_DS_NAME'
    and upcase(NAME) not in ('SITE','SUBSITE','TYPE','SAMPLE','DATE','YEAR','PAIR')
    and upcase(TYPE) ='NUM';
quit;

data WANT;
  set MYLIB.MY_DS_NAME;
  &divide;
run;

Re: divide many columns by same value

PaigeMiller — Wed, 22 Sep 2021 11:17:55 GMT

No transpose needed. Here is an example using SASHELP.CARS

data want;
    set sashelp.cars;
    array x msrp invoice enginesize cylinders horsepower; /* List all desired variables here */
    do i=1 to dim(x);
        x(i)=x(i)/0.1;
    end;
run;

topic Re: divide many columns by same value in SAS Programming

divide many columns by same value

Re: divide many columns by same value

Re: divide many columns by same value

Re: divide many columns by same value