topic Re: new variable from conditional concatenation of observations from other variable in SAS Procedures

new variable from conditional concatenation of observations from other variable

jmrc — Wed, 06 Oct 2021 17:36:16 GMT

Hello everyone, I need help to find a procedure to create a new variable "long_name", which should be populated according to the relation between the variable "code" and the variable "name", the code variable is a drill down of the geographical location in the "name" variable.

Data input:

CODE	NAME
A1	EUROPE
A11	GERMANY
A111	BERLIN
A12	FRANCE
A121	NICE
A122	LYON
A123	PARIS
A2	AFRICA
A21	NIGERIA
A211	LAGOS

Desired output:

CODE	NAME	LONG_NAME
A1	EUROPE	EUROPE
A11	GERMANY	EUROPE_GERMANY
A111	BERLIN	EUROPE_GERMANY_BERLIN
A12	FRANCE	EUROPE_FRANCE
A121	NICE	EUROPE_FRANCE_NICE
A122	LYON	EUROPE_FRANCE_LYON
A123	PARIS	EUROPE_FRANCE_PARIS
A2	AFRICA	AFRICA
A21	NIGERIA	AFRICA_NIGERIA
A211	LAGOS	AFRICA_NIGERIA_LAGOS

Thank you so much in advance.

jmrc

Re: new variable from conditional concatenation of observations from other variable

AMSAS — Wed, 06 Oct 2021 18:06:22 GMT

Look to use the PROC FORMAT CNTLIN and datastep

/* Create a CNTLIN dataset for PROC FORMAT */
/* See https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p0owa4ftikc2ekn1q0rmpulg86cx.htm */

data have ;
	fmtname="$Place" ;
	input start $ label $ ;
	cards ;
A1 Europe
A11 Germany
A111 Berlin
A12 France
A121 Nice
A122 Lyon
A123 Paris
A2 Africa
A21 Nigeria
A211 Lagos
;

/* Create $Place format using the CNTLIN dataset */
proc format cntlin=have ;
run ;

data want ;
	length longName $40. ;
	set have ;
	/* Determine length of variable start */
	strLen=length(start) ;
	/* Loop through start variable to build longName */
	do i=2 to strLen ;
		if i=2 then
			/* First 2 characters of start determines the continent */
			longName=putc(substr(start,1,i),"place.") ;
		else 
			/* 3rd, 4th characters will add country and city */
			longName=trim(longName)||"_"||putc(substr(start,1,i),"place.") ;
		put start= longName= ;
	end ;
	/* write to want dataset */
	output ;
run ;

Re: new variable from conditional concatenation of observations from other variable

jmrc — Wed, 06 Oct 2021 20:09:55 GMT

AMSAS, worked perfectly. Thank you so much.