Re: transposing long to wide - I get too many repeat observations

ansepans · Posted 05-20-2020 05:32 AM

Hi

I am transposing my data to get from wide to long. When I use the code below I get to many copies of the observations. I think I got it right until i merge all my transposed data files. Can anybody tell me what I am doing wrong?

Thank you

proc sort data=bact_all;
by patientId;
run;

proc transpose data=bact_all out=transbact_all1 (drop=_name_ ) prefix=sample_ let;
by patientId sex affiliationid infection;
var samplenr_11-samplenr_18 ;
run;

data bact_all11;
set transbact_all1;
if sample_1="" then delete;
run;

proc transpose data=bact_all out=transbact_all2 (drop=_name_ ) prefix=sample_2 let;
by patientId sex affiliationid infection;
var samplenr_21-samplenr_24;
run;

data bact_all22;
set transbact_all2;
if sample_21="" then delete;
run;

proc transpose data=bact_all out=transbact_all3 (drop=_name_ ) prefix=sample_3 let;
by patientId sex affiliationid infection;
var samplenr_31-samplenr_33;
run;

data bact_all33;
set transbact_all3;
if sample_31="" then delete;
run;

proc transpose data=bact_all out=transbact_all4 (drop=_name_ ) prefix=sample_4 let;
by patientId sex affiliationid infection;
var samplenr_41-samplenr_44;
run;

data bact_all44;
set transbact_all4;
*if sample_41="" then delete;
run;

data bact_all1;
merge transbact_all1 (rename=(sample_1=sample1) )
transbact_all2 (rename=(sample_21=sample2) )
transbact_all3 (rename=(sample_31=sample3) )
transbact_all4 (rename=(sample_41=sample4) );
by patientId;
run;

data bact_all2;
merge bact_all11 (rename=(sample_1=sample1) )
bact_all22 (rename=(sample_21=sample2) )
bact_all33 (rename=(sample_31=sample3) )
bact_all44 (rename=(sample_41=sample4) );
by patientId;
run;

ed_sas_member · Posted 05-20-2020 05:36 AM

Hi @ansepans

Could you please post your sample data as datalines in a DATA step?

Indeed, most of us won't open the Excel file for security purpose.

Best,

ansepans · Posted 05-20-2020 08:24 AM

Ok, here is a new version of my data. I hope this is readable.

patientid affiliationid contamination sex samplenr_11 samplenr_12 samplenr_13 samplenr_14 samplenr_15 samplenr_16 samplenr_17 samplenr_18 samplenr_21 samplenr_22 samplenr_23 samplenr_24 samplenr_31 samplenr_32 samplenr_33 samplenr_41 samplenr_42 samplenr_43 samplenr_44 infection
522 22752 1 1 . . . . . . . . . . . . . . . . . . . 1
708 22752 1 2 Micrococcus Micrococcus . . . . . . Staph_epidermidis Coryne_bacterium . . Micrococcus Moraxella_species . . . . . 1
958 69167 1 1 staph_epidermidis . . . . . . . . . . . . . . . . . . 1
983 22752 1 2 Staph_epidermidis Micrococcus Moraxella_species . . . . . neisseria_spp . . . . . . . . . . 1
1391 22752 1 1 Staph_epidermidis Staph_epidermidis Coryne_bacterium . . . . . staph_warneri . . . staph_warneri . . . . . . 2
1412 22752 1 2 Staph_epidermidis Staph_hominis Staph_hominis neisseria_spp staph_haemolyticus p_acnes pseudomonas_spp Rothia_mucilaginosa p_acnes . . . staph_haemolyticus . . staph_epidermidis Rothia_mucilagiosa p_acnes staph_hominis 1

PaigeMiller · Posted 05-20-2020 08:31 AM

I think the request was to provide the data as DATA STEP CODE. What you have provided is not code. Please use the running man icon and paste your code into that box. If you need help creating data step code, here are instructions:

How to create a data step version of your data AKA generate sample data for forums

How to convert datasets to data steps

--
Paige Miller

ansepans · Posted 05-20-2020 09:29 AM

like this?

data WORK.BACT_ALL;
infile datalines dsd truncover;
input patientid:BEST. affiliationid:BEST. contamination:BEST. sex:BEST. samplenr_11:$21. samplenr_12:$21. samplenr_13:$27. samplenr_14:$21. samplenr_15:$11. samplenr_16:$14. samplenr_17:$14. samplenr_18:$19. samplenr_21:$14. samplenr_22:$19. samplenr_23:$1
1. samplenr_24:$1. samplenr_31:$19. samplenr_32:$17. samplenr_33:$1. samplenr_41:$15. samplenr_42:$18. samplenr_43:$14. samplenr_44:$14. infection:15.;
format patientid BEST. affiliationid BEST. contamination BEST. sex BEST. infection 15.;
label patientid="patientid" affiliationid="affiliationid" contamination="contamination" sex="sex" samplenr_11="samplenr_11" samplenr_12="samplenr_12" samplenr_13="samplenr_13" samplenr_14="samplenr_14" samplenr_15="samplenr_15" samplenr_16="samplenr_16" sa
mplenr_17="samplenr_17" samplenr_18="samplenr_18" samplenr_21="samplenr_21" samplenr_22="samplenr_22" samplenr_23="samplenr_23" samplenr_24="samplenr_24" samplenr_31="samplenr_31" samplenr_32="samplenr_32" samplenr_33="samplenr_33" samplenr_41="samplenr_41
" samplenr_42="samplenr_42" samplenr_43="samplenr_43" samplenr_44="samplenr_44" infection="infection";
datalines;
522 22752 1 1 1
708 22752 1 2 Micrococcus Micrococcus 1
958 69167 1 1 staph epidermidis 1
983 22752 1 2 Staph. epidermidis Micrococcus Moraxella species 1
1391 22752 1 1 Staph. epidermidis Staph. epidermidis Coryne bacterium spp. 2
1412 22752 1 2 Staph. epidermidis Staph. hominis Staph. hominis 1
1421 22752 1 2 staph epidermidis 1
1475 22752 2 2 Coryne bacterium spp. 1
1642 22752 1 1 Staph. capitis Staph. epidermidis Staph. hominis Staph. warneri Micrococcus Staph. epidermidis Bacillus subtilis 1
1684 22752 1 1 staph epidermidis 1
1735 22752 1 1 staph epidermidis 1
1755 22752 2 2 staph epidermidis Moraxella spp. staph. Hominis Micrococcus staph. Capitis Aerococcus spp. staph. Epidermidis staph. Hominis staph. Capitis 2
1863 72911 1 1 staph epidermidis 2
1998 69167 1 1 staph epidermidis 2
2008 69167 1 2 CoNS Coryne bacterium spp. 1
2269 69167 1 2 Staph. haemolyticus Staph. hominis Micrococcus Coryne bacterium spp. 1
2308 69167 1 2 Staph. hominis Micrococcus Coryne bacterium spp. 1
2362 22752 1 1 2
2510 22752 2 2 Staph. capitis Staph. Haemolyticus Staph. Haemolyticus 1
2559 22752 1 2 Staph. hominis Micrococcus 1
2570 22752 1 2 Staph. hominis Bacillus species 1
2873 22752 2 2 Coryne bacterium spp. Micrococcus CoNS Staph. epidermidis CoNS Strep. Dysgalactiae 1
2938 22752 1 1 2
3030 22752 2 1 Staph. capitis Staph. hominis staph. haemolyticus Staph. capitis Staph. capitis 1
;;;;

ballardw · Posted 05-20-2020 10:25 AM

Now paste that code into a code box. On the forum that is opened with either the </> or "running man" icons (the 7th or 8th icon across the top of the box).

The main message windows reformat pasted text and that generally means that white space is removed and other things. The DSD option requires two spaces between values for the input to work with space delimited text and they have been removed so that data step will not run.

Also the line breaks introduced have your informat for samplenr_23 invalid and likely a few other things.

ansepans · Posted 05-20-2020 10:56 AM

 data WORK.BACT_ALL;
   infile datalines dsd truncover;
 input patientid:BEST. affiliationid:BEST. contamination:BEST. sex:BEST. samplenr_11:$21. samplenr_12:$21. samplenr_13:$27. samplenr_14:$21. samplenr_15:$11. samplenr_16:$14. samplenr_17:$14. samplenr_18:$19. samplenr_21:$14. samplenr_22:$19. samplenr_23:$1
 1. samplenr_24:$1. samplenr_31:$19. samplenr_32:$17. samplenr_33:$1. samplenr_41:$15. samplenr_42:$18. samplenr_43:$14. samplenr_44:$14. infection:15.;
   format patientid BEST. affiliationid BEST. contamination BEST. sex BEST. infection 15.;
 label patientid="patientid" affiliationid="affiliationid" contamination="contamination" sex="sex" samplenr_11="samplenr_11" samplenr_12="samplenr_12" samplenr_13="samplenr_13" samplenr_14="samplenr_14" samplenr_15="samplenr_15" samplenr_16="samplenr_16" sa
 mplenr_17="samplenr_17" samplenr_18="samplenr_18" samplenr_21="samplenr_21" samplenr_22="samplenr_22" samplenr_23="samplenr_23" samplenr_24="samplenr_24" samplenr_31="samplenr_31" samplenr_32="samplenr_32" samplenr_33="samplenr_33" samplenr_41="samplenr_41
 " samplenr_42="samplenr_42" samplenr_43="samplenr_43" samplenr_44="samplenr_44" infection="infection";
 datalines;
 522 22752 1 1                                       1
 708 22752 1 2 Micrococcus Micrococcus                                   1
 958 69167 1 1 staph epidermidis                                     1
 983 22752 1 2 Staph. epidermidis Micrococcus Moraxella species                                 1
 1391 22752 1 1 Staph. epidermidis Staph. epidermidis Coryne bacterium spp.                                 2
 1412 22752 1 2 Staph. epidermidis Staph. hominis Staph. hominis                                 1
 1421 22752 1 2 staph epidermidis                                     1
 1475 22752 2 2 Coryne bacterium spp.                                     1
 1642 22752 1 1 Staph. capitis Staph. epidermidis Staph. hominis Staph. warneri Micrococcus               Staph. epidermidis Bacillus subtilis           1
 1684 22752 1 1 staph epidermidis                                     1
 1735 22752 1 1 staph epidermidis                                     1
 1755 22752 2 2 staph epidermidis               Moraxella spp. staph. Hominis Micrococcus   staph. Capitis     Aerococcus spp. staph. Epidermidis staph. Hominis staph. Capitis 2
 1863 72911 1 1 staph epidermidis                                     2
 1998 69167 1 1 staph epidermidis                                     2
 2008 69167 1 2 CoNS Coryne bacterium spp.                                   1
 2269 69167 1 2 Staph. haemolyticus Staph. hominis Micrococcus Coryne bacterium spp.                               1
 2308 69167 1 2 Staph. hominis Micrococcus Coryne bacterium spp.                                 1
 2362 22752 1 1                                       2
 2510 22752 2 2                 Staph. capitis Staph. Haemolyticus     Staph. Haemolyticus             1
 2559 22752 1 2 Staph. hominis Micrococcus                                   1
 2570 22752 1 2 Staph. hominis Bacillus species                                   1
 2873 22752 2 2 Coryne bacterium spp. Micrococcus             CoNS Staph. epidermidis CoNS   Strep. Dysgalactiae             1
 2938 22752 1 1                                       2
 3030 22752 2 1           Staph. capitis Staph. hominis staph. haemolyticus Staph. capitis             Staph. capitis       1
 ;;;;

did I do it right this time? Thank you for your patience

art297 · Posted 05-20-2020 10:59 AM

And, after you post a readable data step that allows us to produce your current dataset, we will also need to see the long data set that you want to achieve from your current example data.

Art, CEO, AnalystFinder.com

art297 · Posted 05-20-2020 05:35 PM

Obviously, the forum's data2datastep macro needs a little more work, as it is currently unable to transform your dataset into a readable datastep.

Below is code that does present your data as a readable datastep. However, I still want to know what you want the resulting long file to look like. I'd even accept an excel version of that desired result.

data WORK.BACT_ALL_HAVE;
  infile datalines4 dsd dlm=',' truncover;
  input patientid affiliationid: contamination sex samplenr_11:$21.
   samplenr_12:$21. samplenr_13:$27. samplenr_14:$21. samplenr_15:$11.
   samplenr_16:$14. samplenr_17:$14. samplenr_18:$19. samplenr_21:$14.
   samplenr_22:$19. samplenr_23:$11. samplenr_24:$1. samplenr_31:$19.
   samplenr_32:$17. samplenr_33:$1. samplenr_41:$15. samplenr_42:$18.
   samplenr_43:$14. samplenr_44:$14. infection;
  label patientid="patientid" affiliationid="affiliationid"
   contamination="contamination" sex="sex" samplenr_11="samplenr_11"
   samplenr_12="samplenr_12" samplenr_13="samplenr_13"
   samplenr_14="samplenr_14" samplenr_15="samplenr_15"
   samplenr_16="samplenr_16" samplenr_17="samplenr_17"
   samplenr_18="samplenr_18" samplenr_21="samplenr_21" 
   samplenr_22="samplenr_22" samplenr_23="samplenr_23" 
   samplenr_24="samplenr_24" samplenr_31="samplenr_31"
   samplenr_32="samplenr_32" samplenr_33="samplenr_33"
   samplenr_41="samplenr_41" samplenr_42="samplenr_42"
   samplenr_43="samplenr_43" samplenr_44="samplenr_44"
   infection="infection";
   datalines4;
522,  22752, 1, 1, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
708,  22752, 1, 2, "Micrococcus", "Micrococcus", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
958,  69167, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
983,  22752, 1, 2, "Staph. epidermidis", "Micrococcus", "Moraxella species", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1391, 22752, 1, 1, "Staph. epidermidis", "Staph. epidermidis", "Coryne bacterium spp.", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 2
1412, 22752, 1, 2, "Staph. epidermidis", "Staph. hominis", "Staph. hominis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1421, 22752, 1, 2, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1475, 22752, 2, 2, "Coryne bacterium spp.", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1642, 22752, 1, 1, "Staph. capitis", "Staph. epidermidis", "Staph. hominis", "Staph. warneri", "Micrococcus", "", "", "", "", "", "", "", "Staph. epidermidis", "Bacillus subtilis", "", "", "", "", "", 1
1684, 22752, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1735, 22752, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
1755, 22752, 2, 2, "staph epidermidis", "", "", "", "", "", "", "", "Moraxella spp.", "staph. Hominis", "Micrococcus", "", "staph. Capitis", "", "", "Aerococcus spp.", "staph. Epidermidis", "staph. Hominis", "staph. Capitis", 2
1863, 72911, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 2
1998, 69167, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 2
2008, 69167, 1, 2, "CoNS", "Coryne bacterium spp.", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
2269, 69167, 1, 2, "Staph. haemolyticus", "Staph. hominis", "Micrococcus", "Coryne bacterium spp.", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
2308, 69167, 1, 2, "Staph. hominis", "Micrococcus", "Coryne bacterium spp.", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
2362, 22752, 1, 1, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 2
2510, 22752, 2, 2, "", "", "", "", "", "", "", "", "Staph. capitis", "Staph. Haemolyticus", "", "", "Staph. Haemolyticus", "", "", "", "", "", "", 1
2559, 22752, 1, 2, "Staph. hominis", "Micrococcus", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
2570, 22752, 1, 2, "Staph. hominis", "Bacillus species", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
2873, 22752, 2, 2, "Coryne bacterium spp.", "Micrococcus", "", "", "", "", "", "", "CoNS", "Staph. epidermidis", "CoNS", "", "Strep. Dysgalactiae", "", "", "", "", "", "", 1
2938, 22752, 1, 1, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 2
3030, 22752, 2, 1, "", "", "", "", "", "Staph. capitis", "Staph. hominis", "staph. haemolyticus", "Staph. capitis", "", "", "", "", "", "", "Staph. capitis", "", "", "", 1
3035, 69167, 1, 1, "CoNS", "Staph. capitis", "Acinetobacter (ssp. lwoffi)", "Staph. capitis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
3116, 69167, 1, 1, "staph epidermidis", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 1
;;;;
run;

Art, CEO, AnalystFinder.com

Tom · Posted 05-21-2020 11:38 AM

Try this macro instead for making code you can post that re-creates a dataset.

filename ds2post url 'http://tiny.cc/ds2post';
%include ds2post;
%ds2post();

If you give it a dataset that doesn't exist or just -H as the dataset name then you will get a brief description of how to use it.

1805  %ds2post(-h);

%DS2POST - SAS macro to copy data into a SAS Data Step in a form which you can post to on-line forums.

Syntax: %ds2post(data,file,obs,target)

 data   = Name of SAS dataset (or view) that you want to output. Default is last created dataset.
 Use data=-help to print instructions.

 file   = Fileref or quoted physical filename for code. Default of file=log will print code to the SAS log.
 file=print will print code to results.

 obs    = Number of observations to output. Default obs=20. Use obs=MAX to copy complete dataset.

 target = Name to use for generated dataset. Default is to make work dataset using the name of the input.

For more information see source code available on github at
https://raw.githubusercontent.com/sasutils/macros/master/ds2post.sas

art297 · Posted 05-21-2020 05:23 PM

@Tom: Did you write that macro? Definitely worked better than the one that was proposed in this thread.

Art, CEO, AnalystFinder.com

ansepans · Posted 05-21-2020 04:40 AM

 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?

art297 · Posted 05-21-2020 10:04 AM

You still haven't shown what you actually want bact_all1 to look like, so we can only guess.

You don't mention the variable contamination in any of your examples, but I included it below. Does the following achieve what you are trying to accomplish?:

proc import datafile="/folders/myfolders/bact_all.xlsx"
            out=bact_all replace dbms=xlsx;
  getnames=yes;
run;

filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=bact_all, out=bact_all1,
  by=patientId sex affiliationid infection contamination,
  id=num, missing=yes,
  var=samplenr_1 samplenr_2 samplenr_3 samplenr_4)

If it does, great! If it doesn't, please indicate how it differs from what you want.

Art, CEO, AnalystFinder.com

Classroom Training Available!