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;
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,
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
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
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.
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
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
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
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
@Tom: Did you write that macro? Definitely worked better than the one that was proposed in this thread.
Art, CEO, AnalystFinder.com
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: