SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Leiviboy
Calcite | Level 5

Hi,

 

I have a table in SAS that looks like this:

 

Leiviboy_1-1679415885134.png

Then I want the have one value for each column like this: 

 

permutationfirst_statsecond_statthird_stat
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage
6721MultishotCritical ChanceCritical Damage

 

Anyone know how to do this with code?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Here is one way to provide example data as a data step. I'm using delimited with , because I have no idea what you actually have but the comma helps with some issues around reading the spaces embedded in string values.

data example;
   infile datalines dlm=',';
   informat permutation f8. first_stat second_stat third_stat $15.;
   input permutation first_stat second_stat third_stat ;
datalines;
6721,Multishot,Critical Chance,Critical Damage
6721,Critical Chance,Multishot,Critical Damage
6721,Critical Damage,Multishot,Critical Chance
6721,Critical Damage,Critical Chance,Multishot
;

If the request is actually to re-order the values then perhaps:

data want;
   set example;
   call sortc(third_stat, second_stat, first_stat);
run;

The Call Sortc function sorts the variables shown so that the result will have the lowest sort order value in the first parameter, the second lowest in the second and so on. The order you showed implies that you want the first_stat to have the highest sort value (M comes after C) and it happens that "Damage" would come after "Chance" so that determined the order of the variables in the call.

 

Is this related to a role-playing game by any chance?

[Admin - Added documentation link to SORTC Function]

View solution in original post

5 REPLIES 5
AMSAS
SAS Super FREQ

Can you reword your request, as I do not understand how you are going from your input "have" data to your output "want" data
If this helps:

  1. What are the rules that you are applying
  2. Why does your "want" data have multiple identical rows
  3. What have you tried? Provide sample data, code and the log

Basically the more information you provide in as simple form as possible the greater the chance you will get a helpful response.

Here's an example (not related to this post)

/* Create sample "have" data */
data have ;
	format 
		date1 date.
		date2 date.
	;
	do i=1 to 10 ;
		date1 =today()+int(ranuni(1)*30) ;
		date2 =today()+int(ranuni(2)*30) ;
		output have ;
	end ;
run ;

/* What I tried */

proc sql ;
	create table want as
 	select intck('day',date1, date2) as c_los
 	from have 
	where c_los< 10 ;
;
quit ;
run ;

 

PaigeMiller
Diamond | Level 26

We can't work with data in screen captures. You will need to provide a portion of your data as working SAS data step code (instructions), and not in any other format.

--
Paige Miller
ballardw
Super User

Here is one way to provide example data as a data step. I'm using delimited with , because I have no idea what you actually have but the comma helps with some issues around reading the spaces embedded in string values.

data example;
   infile datalines dlm=',';
   informat permutation f8. first_stat second_stat third_stat $15.;
   input permutation first_stat second_stat third_stat ;
datalines;
6721,Multishot,Critical Chance,Critical Damage
6721,Critical Chance,Multishot,Critical Damage
6721,Critical Damage,Multishot,Critical Chance
6721,Critical Damage,Critical Chance,Multishot
;

If the request is actually to re-order the values then perhaps:

data want;
   set example;
   call sortc(third_stat, second_stat, first_stat);
run;

The Call Sortc function sorts the variables shown so that the result will have the lowest sort order value in the first parameter, the second lowest in the second and so on. The order you showed implies that you want the first_stat to have the highest sort value (M comes after C) and it happens that "Damage" would come after "Chance" so that determined the order of the variables in the call.

 

Is this related to a role-playing game by any chance?

[Admin - Added documentation link to SORTC Function]

Leiviboy
Calcite | Level 5

That is a very good solution

Leiviboy
Calcite | Level 5
Yes it's related to a game

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 707 views
  • 3 likes
  • 4 in conversation