topic Re: How to create Variable ID and increment in Datastep or Proc sql? in SAS Programming

How to create Variable ID and increment in Datastep or Proc sql?

MarkWik — Sat, 04 May 2013 07:40:08 GMT

Hi folks,

How to create Variable ID and increment in Datastep or Proc sql?

I have a requirement to create a variable called id and that should have values as stated in the below example. Any help would be greatly appreciated. Also is there a way to make it generic by automating with a Macro or inside a macro?

ABC.DEF.01

ABC.DEF.02

ABC.DEF.03

ABC.DEF.04

-.-----and so on.

or in other cases its like

ABC.DEF.A1

ABC.DEF.A2

ABC.DEF.A3

...___ and so on

My thought is prolly to create 2 different columns, one with ABC.DEF and the other with 01.... and then concatenate them or maybe retain and increment?Well, I am not quite getting though.

Re: How to create Variable ID and increment in Datastep or Proc sql?

RichardinOz — Sat, 04 May 2013 10:35:10 GMT

Are you aware that your first example will only provide 99 distinct ID values? And your second only around 240? Or if I interpret you correctly and both are to be part of the same series you will only get just over 340 values?

The first example is easy:

Data want ;

Length ID $ 10 ;

set have (obs = 99) ;

ID = 'ABC.DEF.' || PUT (_N_, Z2.) ;

Run ;

Richard

Re: How to create Variable ID and increment in Datastep or Proc sql?

RichardinOz — Sat, 04 May 2013 10:50:02 GMT

For the second part

Data wantmore ;

set have (firstobs = 100 obs = 343) ;

Retain alpha 65 numero 0 ;

numero + 1 ;

ID = 'ABC.DEF.' || trim (byte (alpha)) || put (numero, 1.) ;

if numero = 9 then

do ;

alpha + 1 ;

numero = 0

end ;

Run ;

Alert! untested code...

Richard

Re: How to create Variable ID and increment in Datastep or Proc sql?

RichardinOz — Sat, 04 May 2013 10:51:46 GMT

Oh, above code designed for Windows, should work on Unix; but on zOS you will have to look up the EBCDIC collating sequence and deal with the split in the alphabet.

Richard

Re: How to create Variable ID and increment in Datastep or Proc sql?

MarkWik — Sun, 05 May 2013 06:53:54 GMT

Hi Richard,

Thanks very much indeed. That was so helpful and well explained too. Cheers mate. I didn't know z2. format would give leading zeros.hmmmm!!!.

Thanks for taking the time to answer on a weekend,

Mark