BookmarkSubscribeRSS Feed
ak2011
Fluorite | Level 6
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
3 REPLIES 3
ak2011
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

ak2011
Fluorite | Level 6
Sorry, I was away for a while. Thanks for your response.
ak.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 554 views
  • 0 likes
  • 2 in conversation