topic Re: Applying combinations to a column in excel in SAS Programming

Applying combinations to a column in excel

thelearner — Tue, 21 Aug 2018 13:30:05 GMT

I have a excel file in which i have a column as policy no in this way-

polnr

786543,0786543

957432,00957432

0746352,746352

937526,000937526

X04578,0X04578

I want to add a condition in my code which will make 4 combinations of each policy no such as with no zero,with 1 leading zero,with 2 leading zero and 3 leading zero.no matter what combinations it has i just want this 4 combinations.

i made a below code but it isnt working for these.just for the ref here it is

data ds2;
length pol $100;
set ds;
if (anydigit(pol)<1) then output;
else do;
do while(char(pol,1)='0');
pol=substrn(pol,2);
end;
output;
pol="0"||strip(pol);
output;
pol="0"||strip(pol);
output;
pol="0"||strip(pol);
output;
end;
run;

please help me.

Regards:)

Re: Applying combinations to a column in excel

PeterClemmensen — Tue, 21 Aug 2018 13:57:10 GMT

One Way..

data have;
input polnr $;
datalines;
786543
0786543
957432
00957432
0746352
746352
937526
000937526
X04578
0X04578
;

data want(drop=i);
   set have;
   pol =polnr;
   output;
   do i=1 to 3;
      pol =cats('0', pol );
      output;
   end;
run;

Re: Applying combinations to a column in excel

RW9 — Tue, 21 Aug 2018 14:00:16 GMT

Please post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And what you want to see out at the end. Its hard to guess what you have /want? I.e. you could have/want:

data have;
  length polnr $20;
  input polnr $;
datalines;
786543
0786543
;
run;

data want;
  set have;
  array res{4} $20;
  do i=1 to 4;
    res{i}=cats(repeat("0",i),polnr);
  end;
run;

Re: Applying combinations to a column in excel

thelearner — Tue, 21 Aug 2018 14:04:15 GMT

i have 2 different combination in same column separated by ',' in one single line in excel.and from those 2 combination i want to make 4 different combination.

no matter how many combinations i have i just want to make it 4

Re: Applying combinations to a column in excel

PeterClemmensen — Tue, 21 Aug 2018 14:10:04 GMT

Just to be sure then.. So if you have one record with two combinations with 0 and 2 leading zeros respectively, then you want to add two extra combinations with 1 and 3 leading zeros respectively, correct?

Re: Applying combinations to a column in excel

RW9 — Tue, 21 Aug 2018 14:12:50 GMT

Post have dataset as a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Post what you want out from this data. Not going to guess any further.

Re: Applying combinations to a column in excel

thelearner — Tue, 21 Aug 2018 14:13:10 GMT

exactly.but in the same line itself.which should be separated by ','.as its an excel file.

Re: Applying combinations to a column in excel

PeterClemmensen — Tue, 21 Aug 2018 14:22:45 GMT

Can I assume that your data is representative? Or can you also have 0 combinations? Or 1? Or all 4?

Re: Applying combinations to a column in excel

thelearner — Tue, 21 Aug 2018 14:55:16 GMT

yes it my have any combination.may be 1 or may be 2 or 3 or 4 as well.