Proc transpose issues in dataset 2 not in dataset 1

ak2011 — Thu, 12 Dec 2019 09:43:11 GMT

Hi, I would greatly appreciate if someone could help me with the SAS code to solve this problem in dataset 2. Eventually, I would like to have the same output for dataset 2 as in dataset 1 below: The main problem lies with the proc transpose in dataset 2. Please, output for datasets 1 and 2 are attached. Errors were obtained in the proc transpose for dataset 2; SAS produced only 3 obs for the output. The ultimate aim is to find the frequencies of associations between the pollutants.No errors for dataset 1. Thanks. ak. /*DATASET 1*/ data idnew1; input id$ job idchem; datalines; os1 1 990005 . os1 1 990021 os1 1 211700 os1 2 211700 os1 2 990021 os1 2 210701 os1 2 990005 os2 1 210701 os2 1 990005 os2 2 990021 os2 3 210701 os2 3 990005 os3 3 210701 os3 1 211700 os4 1 210701 os4 1 990005 os4 1 211700 ; 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 /*DATASET 1*/ 74 data idnew1; 75 input id$ job idchem; 76 datalines; NOTE: The data set WORK.IDNEW1 has 17 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 94 ; 95 96 proc format; 97 value idchem 98 990005 = "cla_exp" 99 990021 = "bio_exp" 100 210701 = "amo_exp" 101 211700 = "chl_exp"; NOTE: Format IDCHEM is already on the library WORK.FORMATS. NOTE: Format IDCHEM has been output. 102 run; NOTE: PROCEDURE FORMAT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 103 104 data temp; 105 set idnew1; 106 dum = 1; 107 format idchem idchem.; 108 id_job=catx('_', id, job); 109 put _all_; 110 run; id=os1 job=1 idchem=cla_exp dum=1 id_job=os1_1 _ERROR_=0 _N_=1 id=os1 job=1 idchem=bio_exp dum=1 id_job=os1_1 _ERROR_=0 _N_=2 id=os1 job=1 idchem=chl_exp dum=1 id_job=os1_1 _ERROR_=0 _N_=3 id=os1 job=2 idchem=chl_exp dum=1 id_job=os1_2 _ERROR_=0 _N_=4 id=os1 job=2 idchem=bio_exp dum=1 id_job=os1_2 _ERROR_=0 _N_=5 id=os1 job=2 idchem=amo_exp dum=1 id_job=os1_2 _ERROR_=0 _N_=6 id=os1 job=2 idchem=cla_exp dum=1 id_job=os1_2 _ERROR_=0 _N_=7 id=os2 job=1 idchem=amo_exp dum=1 id_job=os2_1 _ERROR_=0 _N_=8 id=os2 job=1 idchem=cla_exp dum=1 id_job=os2_1 _ERROR_=0 _N_=9 id=os2 job=2 idchem=bio_exp dum=1 id_job=os2_2 _ERROR_=0 _N_=10 id=os2 job=3 idchem=amo_exp dum=1 id_job=os2_3 _ERROR_=0 _N_=11 id=os2 job=3 idchem=cla_exp dum=1 id_job=os2_3 _ERROR_=0 _N_=12 id=os3 job=3 idchem=amo_exp dum=1 id_job=os3_3 _ERROR_=0 _N_=13 id=os3 job=1 idchem=chl_exp dum=1 id_job=os3_1 _ERROR_=0 _N_=14 id=os4 job=1 idchem=amo_exp dum=1 id_job=os4_1 _ERROR_=0 _N_=15 id=os4 job=1 idchem=cla_exp dum=1 id_job=os4_1 _ERROR_=0 _N_=16 id=os4 job=1 idchem=chl_exp dum=1 id_job=os4_1 _ERROR_=0 _N_=17 NOTE: There were 17 observations read from the data set WORK.IDNEW1. NOTE: The data set WORK.TEMP has 17 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 111 112 proc sort data=temp; by id job id_job; run; NOTE: There were 17 observations read from the data set WORK.TEMP. NOTE: The data set WORK.TEMP has 17 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 113 114 proc transpose data=temp out=idnew2(drop=_name_); 115 by id job id_job; 116 id idchem; 117 var dum; 118 run; NOTE: There were 17 observations read from the data set WORK.TEMP. NOTE: The data set WORK.IDNEW2 has 8 observations and 7 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 119 proc print data=idnew2; 120 Title "Table 1: Merged exposure files for cla, bio, amo and chl pollutants"; 121 run; NOTE: There were 8 observations read from the data set WORK.IDNEW2. NOTE: PROCEDURE PRINT used (Total process time): real time 0.32 seconds cpu time 0.31 seconds 122 123 /*Replacing missing values(.)with zeros(0) ie. unexposed*/ 124 data t; 125 set idnew2; NOTE: There were 8 observations read from the data set WORK.IDNEW2. NOTE: The data set WORK.T has 8 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 126 proc stdize out=t2 reponly missing=0; 127 drop _type_; NOTE: The DROP and KEEP statements are not supported in procedure steps in this release of the SAS System. Therefore, these statements are ignored. 128 run; NOTE: No VAR statement is given. All numerical variables not named elsewhere make up the first set of variables. NOTE: There were 8 observations read from the data set WORK.T. NOTE: The data set WORK.T2 has 8 observations and 7 variables. NOTE: PROCEDURE STDIZE used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 129 proc sort; by id job; 130 Title " Table 2: Merged exposure files for cla, bio, amo and chl pollutants: missing values replaced with zeros"; 131 NOTE: There were 8 observations read from the data set WORK.T2. NOTE: The data set WORK.T2 has 8 observations and 7 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 132 proc print data=t2; run; NOTE: There were 8 observations read from the data set WORK.T2. NOTE: PROCEDURE PRINT used (Total process time): real time 0.11 seconds cpu time 0.12 seconds 133 134 /*FINDING FREQUENCIES OF ASSOCIATIONS*/ 135 proc freq data=t2; 136 tables 137 cla_exp*bio_exp 138 cla_exp*amo_exp 139 cla_exp*chl_exp 140 bio_exp*amo_exp 141 bio_exp*chl_exp 142 bio_exp*chl_exp; 143 144 Title "Frequencies of associations between cla, bio, amo and chl pollutants"; 145 run; NOTE: There were 8 observations read from the data set WORK.T2. NOTE: PROCEDURE FREQ used (Total process time): real time 0.47 seconds cpu time 0.46 seconds 146 147 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 159 ---------------- INFO ON DATASET 2 BELOW,please------------------- /*DATASET 2*/ data env; input id$ job idchem expyr; datalines; ps1 1 210701 82 ps1 1 210701 84 ps1 1 210701 74 ps1 1 430102 79 ps1 1 430102 62 ps1 4 210701 93 ps5 4 210701 78 ps5 4 460003 86 ps5 4 460003 77 ps5 4 520299 88 ps5 4 520299 76 ps5 5 520299 70 ps7 5 520299 93 ps7 1 430101 85 ps7 1 430101 74 ps7 1 430101 72 ps8 1 460003 74 ps8 1 460003 71 ; 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 /*DATASET 2*/ 74 data env; 75 input id$ job idchem expyr; 76 datalines; NOTE: The data set WORK.ENV has 18 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 95 ; 96 97 proc format; 98 value idchem 99 210701 = "amo_exp" 100 430102 = "tol_exp" 101 460003 = "sol_exp" 102 520299 = "ali_exp" 103 430101 = "ben_exp"; NOTE: Format IDCHEM is already on the library WORK.FORMATS. NOTE: Format IDCHEM has been output. 104 run; NOTE: PROCEDURE FORMAT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 105 106 data temp; 107 set env; 108 dum = 1; 109 format idchem idchem.; 110 id_job=catx('_', id, job); 111 put _all_; 112 run; id=ps1 job=1 idchem=amo_exp expyr=82 dum=1 id_job=ps1_1 _ERROR_=0 _N_=1 id=ps1 job=1 idchem=amo_exp expyr=84 dum=1 id_job=ps1_1 _ERROR_=0 _N_=2 id=ps1 job=1 idchem=amo_exp expyr=74 dum=1 id_job=ps1_1 _ERROR_=0 _N_=3 id=ps1 job=1 idchem=tol_exp expyr=79 dum=1 id_job=ps1_1 _ERROR_=0 _N_=4 id=ps1 job=1 idchem=tol_exp expyr=62 dum=1 id_job=ps1_1 _ERROR_=0 _N_=5 id=ps1 job=4 idchem=amo_exp expyr=93 dum=1 id_job=ps1_4 _ERROR_=0 _N_=6 id=ps5 job=4 idchem=amo_exp expyr=78 dum=1 id_job=ps5_4 _ERROR_=0 _N_=7 id=ps5 job=4 idchem=sol_exp expyr=86 dum=1 id_job=ps5_4 _ERROR_=0 _N_=8 id=ps5 job=4 idchem=sol_exp expyr=77 dum=1 id_job=ps5_4 _ERROR_=0 _N_=9 id=ps5 job=4 idchem=ali_exp expyr=88 dum=1 id_job=ps5_4 _ERROR_=0 _N_=10 id=ps5 job=4 idchem=ali_exp expyr=76 dum=1 id_job=ps5_4 _ERROR_=0 _N_=11 id=ps5 job=5 idchem=ali_exp expyr=70 dum=1 id_job=ps5_5 _ERROR_=0 _N_=12 id=ps7 job=5 idchem=ali_exp expyr=93 dum=1 id_job=ps7_5 _ERROR_=0 _N_=13 id=ps7 job=1 idchem=ben_exp expyr=85 dum=1 id_job=ps7_1 _ERROR_=0 _N_=14 id=ps7 job=1 idchem=ben_exp expyr=74 dum=1 id_job=ps7_1 _ERROR_=0 _N_=15 id=ps7 job=1 idchem=ben_exp expyr=72 dum=1 id_job=ps7_1 _ERROR_=0 _N_=16 id=ps8 job=1 idchem=sol_exp expyr=74 dum=1 id_job=ps8_1 _ERROR_=0 _N_=17 id=ps8 job=1 idchem=sol_exp expyr=71 dum=1 id_job=ps8_1 _ERROR_=0 _N_=18 NOTE: There were 18 observations read from the data set WORK.ENV. NOTE: The data set WORK.TEMP has 18 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 113 114 proc sort data=temp; by id job id_job; run; NOTE: There were 18 observations read from the data set WORK.TEMP. NOTE: The data set WORK.TEMP has 18 observations and 6 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 115 116 proc transpose data=temp out=temp2(drop=_name_); 117 by id job id_job; 118 id idchem; 119 var dum; 120 run; ERROR: The ID value "amo_exp" occurs twice in the same BY group. ERROR: The ID value "amo_exp" occurs twice in the same BY group. ERROR: The ID value "tol_exp" occurs twice in the same BY group. NOTE: The above message was for the following BY group: id=ps1 job=1 id_job=ps1_1 ERROR: The ID value "sol_exp" occurs twice in the same BY group. ERROR: The ID value "ali_exp" occurs twice in the same BY group. NOTE: The above message was for the following BY group: id=ps5 job=4 id_job=ps5_4 ERROR: The ID value "ben_exp" occurs twice in the same BY group. ERROR: The ID value "ben_exp" occurs twice in the same BY group. NOTE: The above message was for the following BY group: id=ps7 job=1 id_job=ps7_1 ERROR: The ID value "sol_exp" occurs twice in the same BY group. NOTE: The above message was for the following BY group: id=ps8 job=1 id_job=ps8_1 WARNING: 4 BY groups omitted due to earlier errors. NOTE: There were 18 observations read from the data set WORK.TEMP. NOTE: The data set WORK.TEMP2 has 3 observations and 8 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 121 122 proc print data=temp2; 123 Title "Dataset 2: Proc transpose results"; 124 run; NOTE: There were 3 observations read from the data set WORK.TEMP2. NOTE: PROCEDURE PRINT used (Total process time): real time 0.15 seconds cpu time 0.15 seconds 125 126 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 138

Re: Proc transpose issues in dataset 2 not in dataset 1(reattachment-dataset1 ouput)

ak2011 — Thu, 12 Dec 2019 09:49:30 GMT

Hi,

I attached output for datasets 1 and 2 in my previous message but I see only dataset 2 attached. Sorry, I reattach output for dataset 1.

Thanks.

ak.

Re: Proc transpose issues in dataset 2 not in dataset 1

Kurt_Bremser — Thu, 12 Dec 2019 10:19:57 GMT

Please edit your post to make it readable.

Post example data as code (data step(s) with datalines); use the "little running man" icon for code, and the {i} for logs or textual data like csv files.

Re: Proc transpose issues in dataset 2 not in dataset 1

ak2011 — Wed, 01 Jan 2020 15:01:15 GMT

Sorry, I was away for a while. Thanks for your response.
ak.

topic Re: Proc transpose issues in dataset 2 not in dataset 1 in SAS Studio

Proc transpose issues in dataset 2 not in dataset 1

Re: Proc transpose issues in dataset 2 not in dataset 1(reattachment-dataset1 ouput)

Re: Proc transpose issues in dataset 2 not in dataset 1

Re: Proc transpose issues in dataset 2 not in dataset 1