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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.