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;
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
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;
÷
run;
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;
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.