topic Re: proc freq from wide data table in SAS Programming

proc freq from wide data table

Dhana18 — Wed, 28 Aug 2019 20:18:21 GMT

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.

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

7/31/2019

Pharyngeal

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

7/31/2019

Urethral

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

7/30/2019

Endocervical

Culture

GC Negative

43676

Pharyngeal

Culture

Not Applicable (e.g. Culture positive for N. Meningitides)

7/30/2019

Endocervical

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

7/30/2019

Pharyngeal

Culture

GC Negative

43676

Urethral

Culture

GC Negative

7/30/2019

Urethral

Culture

GC Negative

7/30/2019

Endocervical

Culture

GC Negative

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

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

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

7/30/2019

Urine

GC nucleic acid amplification test (NAAT)

GC Positive

43676

Urethral

Culture

GC Positive

7/29/2019

Urethral

Culture

GC Positive

7/29/2019

Urethral

Culture

GC Positive

7/29/2019

Rectal

Culture

GC Negative

43675

Pharyngeal

GC nucleic acid amplification test (NAAT)

GC Negative

43675

Urethral

Culture

GC Negative

Re: proc freq from wide data table

Reeza — Wed, 28 Aug 2019 20:27:21 GMT

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-data-AKA-generate/ta-p/258712

@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

Re: proc freq from wide data table

ballardw — Wed, 28 Aug 2019 20:29:59 GMT

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.