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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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