data WORK.BACT_ALL1;
infile datalines dsd truncover;
input patientid:BEST. sex:BEST. affiliationid:BEST. infection:15. _LABEL_:$40. sample1:$27. sample2:$19. sample3:$19. sample4:$18.;
format patientid BEST. sex BEST. affiliationid BEST. infection 15.;
label patientid="patientid" sex="sex" affiliationid="affiliationid" infection="infection" _LABEL_="LABEL OF FORMER VARIABLE";
datalines;
522 1 22752 1 samplenr_41
522 1 22752 1 samplenr_42
522 1 22752 1 samplenr_43
522 1 22752 1 samplenr_44
522 1 22752 1 samplenr_15
522 1 22752 1 samplenr_16
522 1 22752 1 samplenr_17
522 1 22752 1 samplenr_18
708 2 22752 1 samplenr_41 Micrococcus
708 2 22752 1 samplenr_42 Micrococcus
708 2 22752 1 samplenr_43
708 2 22752 1 samplenr_44
708 2 22752 1 samplenr_15
708 2 22752 1 samplenr_16
708 2 22752 1 samplenr_17
708 2 22752 1 samplenr_18
958 1 69167 1 samplenr_41 staph epidermidis
958 1 69167 1 samplenr_42
958 1 69167 1 samplenr_43
958 1 69167 1 samplenr_44
958 1 69167 1 samplenr_15
958 1 69167 1 samplenr_16
958 1 69167 1 samplenr_17
958 1 69167 1 samplenr_18
983 2 22752 1 samplenr_41 Staph. epidermidis
983 2 22752 1 samplenr_42 Micrococcus
983 2 22752 1 samplenr_43 Moraxella species
983 2 22752 1 samplenr_44
983 2 22752 1 samplenr_15
983 2 22752 1 samplenr_16
983 2 22752 1 samplenr_17
983 2 22752 1 samplenr_18
1391 1 22752 2 samplenr_41 Staph. epidermidis
1391 1 22752 2 samplenr_42 Staph. epidermidis
1391 1 22752 2 samplenr_43 Coryne bacterium spp.
1391 1 22752 2 samplenr_44
1391 1 22752 2 samplenr_15
1391 1 22752 2 samplenr_16
1391 1 22752 2 samplenr_17
1391 1 22752 2 samplenr_18
1412 2 22752 1 samplenr_41 Staph. epidermidis
1412 2 22752 1 samplenr_42 Staph. hominis
1412 2 22752 1 samplenr_43 Staph. hominis
1412 2 22752 1 samplenr_44
1412 2 22752 1 samplenr_15
1412 2 22752 1 samplenr_16
1412 2 22752 1 samplenr_17
1412 2 22752 1 samplenr_18
1421 2 22752 1 samplenr_41 staph epidermidis
1421 2 22752 1 samplenr_42
1421 2 22752 1 samplenr_43
1421 2 22752 1 samplenr_44
1421 2 22752 1 samplenr_15
1421 2 22752 1 samplenr_16
1421 2 22752 1 samplenr_17
1421 2 22752 1 samplenr_18
1475 2 22752 1 samplenr_41 Coryne bacterium spp.
1475 2 22752 1 samplenr_42
1475 2 22752 1 samplenr_43
1475 2 22752 1 samplenr_44
1475 2 22752 1 samplenr_15
1475 2 22752 1 samplenr_16
1475 2 22752 1 samplenr_17
1475 2 22752 1 samplenr_18
1642 1 22752 1 samplenr_41 Staph. capitis Staph. epidermidis
1642 1 22752 1 samplenr_42 Staph. epidermidis Bacillus subtilis
1642 1 22752 1 samplenr_43 Staph. hominis
1642 1 22752 1 samplenr_44 Staph. warneri
1642 1 22752 1 samplenr_15 Micrococcus
1642 1 22752 1 samplenr_16
1642 1 22752 1 samplenr_17
1642 1 22752 1 samplenr_18
1684 1 22752 1 samplenr_41 staph epidermidis
1684 1 22752 1 samplenr_42
1684 1 22752 1 samplenr_43
1684 1 22752 1 samplenr_44
1684 1 22752 1 samplenr_15
1684 1 22752 1 samplenr_16
1684 1 22752 1 samplenr_17
1684 1 22752 1 samplenr_18
1735 1 22752 1 samplenr_41 staph epidermidis
1735 1 22752 1 samplenr_42
1735 1 22752 1 samplenr_43
1735 1 22752 1 samplenr_44
1735 1 22752 1 samplenr_15
1735 1 22752 1 samplenr_16
1735 1 22752 1 samplenr_17
1735 1 22752 1 samplenr_18
1755 2 22752 2 samplenr_41 staph epidermidis Moraxella spp. staph. Capitis Aerococcus spp.
1755 2 22752 2 samplenr_42 staph. Hominis staph. Epidermidis
1755 2 22752 2 samplenr_43 Micrococcus staph. Hominis
1755 2 22752 2 samplenr_44 staph. Capitis
1755 2 22752 2 samplenr_15 staph. Capitis
1755 2 22752 2 samplenr_16 staph. Capitis
1755 2 22752 2 samplenr_17 staph. Capitis
1755 2 22752 2 samplenr_18 staph. Capitis
1863 1 72911 2 samplenr_41 staph epidermidis
1863 1 72911 2 samplenr_42
1863 1 72911 2 samplenr_43
1863 1 72911 2 samplenr_44
1863 1 72911 2 samplenr_15
1863 1 72911 2 samplenr_16
1863 1 72911 2 samplenr_17
1863 1 72911 2 samplenr_18
1998 1 69167 2 samplenr_41 staph epidermidis
;;;; Wow, I am nearely giving up on myself here. The data in the databox is what I get, when I use the abovementioned code. If you look at patient id 1755 i get "staph. capitis" five times in sample_4 when it should only appear once. When I do the transposing it appears once, so something is happening in the merging step. I would like the sample_11-samplenr_18 to be in the column called sample_1 and samplenr_21-samplenr_24 to be in the column called sample_2 and samplenr_31-sample_33 in column sample_3 and samplemr_41-samplenr_44 in column sample 4. So for patientid 1755 in sample_1 it would say "staph epidermidis" and in sample_2 it would say "moraxella spp" "staph hominis" "micrococcus" in sample_3 "staph capitis" and ind sample_4 it would say "Aerococcus spp" "staph. epidermidis" "staph hominis" "staph capitis" Does it make sense?
... View more