topic Re: update blank columns from existing columns in SAS Enterprise Guide

update blank columns from existing columns

learner_sas — Fri, 02 Sep 2016 20:26:22 GMT

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.

Re: update blank columns from existing columns

ballardw — Fri, 02 Sep 2016 21:13:30 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: update blank columns from existing columns

ballardw — Fri, 02 Sep 2016 21:36:10 GMT

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;

Re: update blank columns from existing columns

learner_sas — Fri, 02 Sep 2016 23:41:37 GMT

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

Re: update blank columns from existing columns

Ksharp — Sat, 03 Sep 2016 02:35:38 GMT


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;

Re: update blank columns from existing columns

learner_sas — Sat, 03 Sep 2016 11:31:02 GMT

Thank you @Ksharp. It worked for me.