🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 10-06-2021 01:36 PM
(723 views)
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
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
AMSAS, worked perfectly. Thank you so much.