cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dhana18
Obsidian | Level 7 Dhana18
Obsidian | Level 7
Go to Solution

proc freq from wide data table

Posted 08-28-2019 04:18 PM (500 views)

I have data set like this. I want to know number of specimens type testtype and results.  And number of specimen type testtype and resluts by id.  Your help in this would be appreciated. 

IDCollDate1Specimen1TestType1Result1CollDate2Specimen2TestType2Result2CollDate3Specimen3TestType3Result3CollDate4Specimen4TestType4Result4CollDate5Specimen5TestType5Result5CollDate6Specimen6TestType6Result6CollDate7Specimen7TestType7Result7CollDate8Specimen8TestType8Result8CollDate9Specimen9TestType9Result8CollDate10Specimen10TestType10Result10CollDate11Specimen11TestType11Result11CollDate12Specimen12TestType12Result12CollDate13Specimen13TestType13Result13CollDate14Specimen14TestType14Result14
17/31/2019PharyngealCultureNot Applicable (e.g. Culture positive for N. Meningitides)                                               
27/31/2019UrethralCultureGC Negative                                                   
37/30/2019UrethralCultureGC Negative                                                   
47/30/2019EndocervicalCultureGC Negative43676PharyngealCultureNot Applicable (e.g. Culture positive for N. Meningitides)                                           
57/30/2019EndocervicalCultureGC Negative                                                   
67/30/2019UrethralCultureGC Negative                                                   
77/30/2019UrethralCultureGC Negative                                                   
87/30/2019UrethralCultureGC Negative                                                   
97/30/2019PharyngealCultureGC Negative43676UrethralCultureGC Negative                                               
107/30/2019UrethralCultureGC Negative                                                   
117/30/2019EndocervicalCultureGC Negative                                                   
127/30/2019PharyngealGC nucleic acid amplification test (NAAT)GC Positive43676UrineGC nucleic acid amplification test (NAAT)GC Positive43676UrethralCultureGC Positive                                           
137/30/2019UrineGC nucleic acid amplification test (NAAT)GC Positive                                                   
147/30/2019UrineGC nucleic acid amplification test (NAAT)GC Positive43676PharyngealGC nucleic acid amplification test (NAAT)GC Positive43676UrethralCultureGC Positive                                           
157/30/2019UrineGC nucleic acid amplification test (NAAT)GC Positive43676UrethralCultureGC Positive                                               
167/29/2019UrethralCultureGC Positive                                                   
177/29/2019UrethralCultureGC Positive                                                   
187/29/2019RectalCultureGC Negative43675PharyngealGC nucleic acid amplification test (NAAT)GC Negative43675UrethralCultureGC Negative                                           
                                                         
0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User ballardw
Super User

Re: proc freq from wide data table

Posted 08-28-2019 04:29 PM (491 views)  |   In reply to Dhana18

Transpose the data so there is one colldate, specimen, testype and result per record.

Then use proc freq.

 

data want; 
   set have;
   array c colldate: ;  /* alternatively Colldate1 - ColldateN */
   array s specimen: ;
   array t testtype: ;
   array r result:   ;
   /* assumes you have the same number of each*/
   do i=1 to dim(c);
      if not missing(c[i]) then do;
         colldate=c[i];
         Specimen = s[i];
         TestType = t[i];
         Result   = r[i];
         output;
      end;
   end;
   keep id colldate specimen testtype result;
run;

proc freq data=want;
   tables specimen*testtype*results/list;
   tables id*specimen*testtype*results/list;
run;

I couldn't tell if you wanted the summary as individuals or combinations. I present the combinations. Remove the * in the first tables statement to get of each variable.

You might want

tables id* (specimen testtype results)/list;

for Id by each of the other variables.

View solution in original post

0 Likes
2 REPLIES 2
Reeza
Super User Reeza
Super User

Re: proc freq from wide data table

Posted 08-28-2019 04:27 PM (492 views)  |   In reply to Dhana18

Expected output from this data? Please also provide sample data as a data step.

 

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

@Dhana18 wrote:

I have data set like this. I want to know number of specimens type testtype and results.  And number of specimen type testtype and resluts by id.  Your help in this would be appreciated. 

ID CollDate1 Specimen1 TestType1 Result1 CollDate2 Specimen2 TestType2 Result2 CollDate3 Specimen3 TestType3 Result3 CollDate4 Specimen4 TestType4 Result4 CollDate5 Specimen5 TestType5 Result5 CollDate6 Specimen6 TestType6 Result6 CollDate7 Specimen7 TestType7 Result7 CollDate8 Specimen8 TestType8 Result8 CollDate9 Specimen9 TestType9 Result8 CollDate10 Specimen10 TestType10 Result10 CollDate11 Specimen11 TestType11 Result11 CollDate12 Specimen12 TestType12 Result12 CollDate13 Specimen13 TestType13 Result13 CollDate14 Specimen14 TestType14 Result14
1 7/31/2019 Pharyngeal Culture Not Applicable (e.g. Culture positive for N. Meningitides)                                                                                              
2 7/31/2019 Urethral Culture GC Negative                                                                                                      
3 7/30/2019 Urethral Culture GC Negative                                                                                                      
4 7/30/2019 Endocervical Culture GC Negative 43676 Pharyngeal Culture Not Applicable (e.g. Culture positive for N. Meningitides)                                                                                      
5 7/30/2019 Endocervical Culture GC Negative                                                                                                      
6 7/30/2019 Urethral Culture GC Negative                                                                                                      
7 7/30/2019 Urethral Culture GC Negative                                                                                                      
8 7/30/2019 Urethral Culture GC Negative                                                                                                      
9 7/30/2019 Pharyngeal Culture GC Negative 43676 Urethral Culture GC Negative                                                                                              
10 7/30/2019 Urethral Culture GC Negative                                                                                                      
11 7/30/2019 Endocervical Culture GC Negative                                                                                                      
12 7/30/2019 Pharyngeal GC nucleic acid amplification test (NAAT) GC Positive 43676 Urine GC nucleic acid amplification test (NAAT) GC Positive 43676 Urethral Culture GC Positive                                                                                      
13 7/30/2019 Urine GC nucleic acid amplification test (NAAT) GC Positive                                                                                                      
14 7/30/2019 Urine GC nucleic acid amplification test (NAAT) GC Positive 43676 Pharyngeal GC nucleic acid amplification test (NAAT) GC Positive 43676 Urethral Culture GC Positive                                                                                      
15 7/30/2019 Urine GC nucleic acid amplification test (NAAT) GC Positive 43676 Urethral Culture GC Positive                                                                                              
16 7/29/2019 Urethral Culture GC Positive                                                                                                      
17 7/29/2019 Urethral Culture GC Positive                                                                                                      
18 7/29/2019 Rectal Culture GC Negative 43675 Pharyngeal GC nucleic acid amplification test (NAAT) GC Negative 43675 Urethral Culture GC Negative                                                                                      
                                                                                                                 

 

 

0 Likes
ballardw
Super User ballardw
Super User

Re: proc freq from wide data table

Posted 08-28-2019 04:29 PM (492 views)  |   In reply to Dhana18

Transpose the data so there is one colldate, specimen, testype and result per record.

Then use proc freq.

 

data want; 
   set have;
   array c colldate: ;  /* alternatively Colldate1 - ColldateN */
   array s specimen: ;
   array t testtype: ;
   array r result:   ;
   /* assumes you have the same number of each*/
   do i=1 to dim(c);
      if not missing(c[i]) then do;
         colldate=c[i];
         Specimen = s[i];
         TestType = t[i];
         Result   = r[i];
         output;
      end;
   end;
   keep id colldate specimen testtype result;
run;

proc freq data=want;
   tables specimen*testtype*results/list;
   tables id*specimen*testtype*results/list;
run;

I couldn't tell if you wanted the summary as individuals or combinations. I present the combinations. Remove the * in the first tables statement to get of each variable.

You might want

tables id* (specimen testtype results)/list;

for Id by each of the other variables.

0 Likes

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • ‎08-28-2019 04:18 PM
  • 501 views
  • 0 likes
  • 3 in conversation