New SAS User

Completely new to SAS or trying something new with SAS? Post here for help getting started.
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Lapis Lazuli | Level 10

Hi guys, 

how can I write in "if, then" statement: if a condition = x then columnx = wordC1 wordC2 wordC3 wordC4 wordC5 wordC6 in a compact way? Let say:

>           if condition = 0 then columnx = wordC1-wordC6         

 

Edit:

suppose to have the following: 

>            ID

>            6

>            6

>            6

>            2

>            2

>            2

>            2

>            1

>            1           

 

Desired output: 

>            out

>            wordC1

>            wordC2

>            wordC3

>            wordP1

>            wordP2

>            wordP3

>            wordP4

>            wordF1

>            wordF2

 

>         if ID = 6 then wordC* (from 1 to 3 because there are 3 rows having 6 in ID column)

>         if ID = 3 then wordP* (from 1 to 4 because there are 4 rows having 2 in ID column)

>         if ID = 1 then wordF* (from 1 to 2 because there are 2 rows having 1 in ID column)

 

 

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

You can use a format (or similar) to store the mapping from ID= 1, 2,6 to C,P, F.  

 

Since your data are grouped by ID , you can use by-group processing to count records in each group.

 

So I think something like below should work, if I'm understanding what you want:

 

 

data have ;
  input id ;
  cards ;
6
6
6
2
2
2
2
1
1
;
run ;

proc format ;
  value type
   6='C'
   2='P'
   1='F'
  ;
run ;

data want ;
  set have ;
  by id notsorted ;

  if first.id then _count=0 ;
  _count++1 ;

  word=cats("word",put(id,type.),_count) ;

  drop _count ;
run ;

proc print data=want ;
run ;

Returns:

 

 

Obs    id     word

 1      6    wordC1
 2      6    wordC2
 3      6    wordC3
 4      2    wordP1
 5      2    wordP2
 6      2    wordP3
 7      2    wordP4
 8      1    wordF1
 9      1    wordF2

 

 

 

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

View solution in original post

8 REPLIES 8
NewUsrStat
Lapis Lazuli | Level 10
I edited the question with example to explain better the desired output
JosvanderVelden
SAS Super FREQ

Something like this?

data _null_;
   length column1 $256.;
   input var1 $ var2 $ var3 $ var4 $ var5 $ var6 $;
   if 1 then column1 = catx(" ", of var1-var6);
   put column1=;
   cards;
word1
word2
word3
word4
word5
word6
;
run;
ballardw
Super User

Where do the C  P and F come from? There is no obvious reason to insert those characters. Is there another rule you haven't mentioned.

 

I suggest you provide an actual example of your data set as I think there may be more than one or two things missing in your description. The data should be in the form of a working data step.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

NewUsrStat
Lapis Lazuli | Level 10
Unfortunately they come from other calculations. I cannot change them. I have to use C, P and F.
Quentin
Super User

You can use a format (or similar) to store the mapping from ID= 1, 2,6 to C,P, F.  

 

Since your data are grouped by ID , you can use by-group processing to count records in each group.

 

So I think something like below should work, if I'm understanding what you want:

 

 

data have ;
  input id ;
  cards ;
6
6
6
2
2
2
2
1
1
;
run ;

proc format ;
  value type
   6='C'
   2='P'
   1='F'
  ;
run ;

data want ;
  set have ;
  by id notsorted ;

  if first.id then _count=0 ;
  _count++1 ;

  word=cats("word",put(id,type.),_count) ;

  drop _count ;
run ;

proc print data=want ;
run ;

Returns:

 

 

Obs    id     word

 1      6    wordC1
 2      6    wordC2
 3      6    wordC3
 4      2    wordP1
 5      2    wordP2
 6      2    wordP3
 7      2    wordP4
 8      1    wordF1
 9      1    wordF2

 

 

 

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
NewUsrStat
Lapis Lazuli | Level 10
Thank you very much! It is what I was looking for!
ballardw
Super User

@NewUsrStat wrote:
Unfortunately they come from other calculations. I cannot change them. I have to use C, P and F.

If they are calculated then you should share the calculation as that is needed as part of the solution of your question.

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1109 views
  • 3 likes
  • 5 in conversation