Hello Everybody
I have a table with DX codes from 1 to 13. I received codes dx1-dx10 from one source and DX11-13 from another sources. Now I want to create a final file which has columns upto DX10 with update. since some of the ids do not have DX codes upto 10 I want to update values from DX11-DX13 into DX1-10 when there is no dx code and I have values in DX11-DX13.
Attached documents has current and expected table structure.
Thank you for your help.
data WORK.TXTFILE; infile datalines dsd truncover; input ID:BEST6. DX1:$7. DX2:$7. DX3:$6. DX4:$6. DX5:$5. DX6:$6. DX7:$6. DX8:$5. DX9:$5. DX10:$5. F12:$1. F13:$1. DX11:$5. DX12:$5. DX13:$5.; datalines4; 147140,O700,O76,Z3A39,Z370,,,,,,,,,D500,D62,D589 141275,I639,I674,F0150,I5042,E854,I10,F17210,I739,I680,K0530,,,Z7982,Z8673,Z9114 120744,O1092,O365930,Z3A40,O99333,O76,O9089,L03311,Z370,O0933,Z23,,,,, 151870,J9621,N170,J9622,R6521,I4901,I4892,I482,I480,B182,B9689,,,D500,D62,D589 161280,Z3831,P0724,P5222,P780,P369,P220,P779,P610,P912,K2971,,,Q443,P8339,P284 129797,Z3801,P220,P0701,P0724,P3639,G039,P271,K4020,,,,,P748,P742,P744 117110,O80,Z370,Z3A39,,,,,,,,,,,, 147647,O480,D649,O99824,O9902,Z370,Z8751,Z3A40,,,,,,Z7982,Z8673,Z9114 120334,O6981X0,O721,O9832,O480,O481,Z87410,O99214,E6601,Z3A41,,,,Z370,, ;;;; run; data want; set txtfile; array x{*} $ dx1-dx10; array y{*} $ dx11-dx13; n=0; do i=1 to dim(x); if missing(x{i}) then do; n+1; if n le dim(y) then x{i}=y{n}; end; end; drop i n; run;
It helps to provide data that we can test code on. Most of us are not interested in typing in data. If you have a small example data set you can follow the instructions at this link: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create datastep text to recreate you dataset that can then be posted here either by pasting into a window or attaching as TXT file.
Maybe something like this:
data want;
set have;
array d dx1-dx10;
array a dx11-dx13;
FirstMiss = findw(catx(',',of d[*]),'.',',','E');
if 0< FirstMiss le 10 then do i=FirstMiss to (min( 10,Firstmiss+2)) ;
index = (i - FirstMiss) +1 ;
d[i] = a[index];
end;
drop firstmiss dx11-dx13;
run;
@ballardw thank you. Will make sure from next time. Below is the input file. I want to update any missing dignosis between DX1-DX10 using DX11-DX13.
data WORK.TXTFILE;
infile datalines dsd truncover;
input ID:BEST6. DX1:$7. DX2:$7. DX3:$6. DX4:$6. DX5:$5. DX6:$6. DX7:$6. DX8:$5. DX9:$5. DX10:$5. F12:$1. F13:$1. DX11:$5. DX12:$5. DX13:$5.;
datalines4;
147140,O700,O76,Z3A39,Z370,,,,,,,,,D500,D62,D589
141275,I639,I674,F0150,I5042,E854,I10,F17210,I739,I680,K0530,,,Z7982,Z8673,Z9114
120744,O1092,O365930,Z3A40,O99333,O76,O9089,L03311,Z370,O0933,Z23,,,,,
151870,J9621,N170,J9622,R6521,I4901,I4892,I482,I480,B182,B9689,,,D500,D62,D589
161280,Z3831,P0724,P5222,P780,P369,P220,P779,P610,P912,K2971,,,Q443,P8339,P284
129797,Z3801,P220,P0701,P0724,P3639,G039,P271,K4020,,,,,P748,P742,P744
117110,O80,Z370,Z3A39,,,,,,,,,,,,
147647,O480,D649,O99824,O9902,Z370,Z8751,Z3A40,,,,,,Z7982,Z8673,Z9114
120334,O6981X0,O721,O9832,O480,O481,Z87410,O99214,E6601,Z3A41,,,,Z370,,
;;;;
run;
data WORK.TXTFILE; infile datalines dsd truncover; input ID:BEST6. DX1:$7. DX2:$7. DX3:$6. DX4:$6. DX5:$5. DX6:$6. DX7:$6. DX8:$5. DX9:$5. DX10:$5. F12:$1. F13:$1. DX11:$5. DX12:$5. DX13:$5.; datalines4; 147140,O700,O76,Z3A39,Z370,,,,,,,,,D500,D62,D589 141275,I639,I674,F0150,I5042,E854,I10,F17210,I739,I680,K0530,,,Z7982,Z8673,Z9114 120744,O1092,O365930,Z3A40,O99333,O76,O9089,L03311,Z370,O0933,Z23,,,,, 151870,J9621,N170,J9622,R6521,I4901,I4892,I482,I480,B182,B9689,,,D500,D62,D589 161280,Z3831,P0724,P5222,P780,P369,P220,P779,P610,P912,K2971,,,Q443,P8339,P284 129797,Z3801,P220,P0701,P0724,P3639,G039,P271,K4020,,,,,P748,P742,P744 117110,O80,Z370,Z3A39,,,,,,,,,,,, 147647,O480,D649,O99824,O9902,Z370,Z8751,Z3A40,,,,,,Z7982,Z8673,Z9114 120334,O6981X0,O721,O9832,O480,O481,Z87410,O99214,E6601,Z3A41,,,,Z370,, ;;;; run; data want; set txtfile; array x{*} $ dx1-dx10; array y{*} $ dx11-dx13; n=0; do i=1 to dim(x); if missing(x{i}) then do; n+1; if n le dim(y) then x{i}=y{n}; end; end; drop i n; run;
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 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.