BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
learner_sas
Quartz | Level 8

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.

 

 


Tables.PNG
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;


View solution in original post

5 REPLIES 5
ballardw
Super User

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.

ballardw
Super User

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;
learner_sas
Quartz | Level 8

@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;

Ksharp
Super User

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;


learner_sas
Quartz | Level 8
Thank you @Ksharp. It worked for me.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2385 views
  • 0 likes
  • 3 in conversation