topic Re: How do i move these values to the intended specific rows and insert blank rows in between them? in SAS Programming

How do i move these values to the intended specific rows and insert blank rows in between them?

Hello_there — Tue, 29 Nov 2022 23:38:11 GMT

Hi i'm trying to move the values in GROUP and PARAM in the same column as the values in criteria and put blank rows in between them. What is the most efficient way to do this?

data have;
infile datalines dsd dlm=",";
	input group $ param $ criteria $ value $;
datalines;
colors, red, >3, 2
colors, blue, <3, 3
cars, ford, >55, 34
cars, chevy, >23, 45
;
run;

desired output:

edited (how it's supposed to really look like):

Re: How do i move these values to the intended specific rows and insert blank rows in between them?

PaigeMiller — Tue, 29 Nov 2022 22:30:01 GMT

Why do you want to change your data set to this really unusual and difficult-to-work-with arrangement?

Is it for some sort of table?

Can you provide more of the actual problem, rather than then details of SAS coding?

Re: How do i move these values to the intended specific rows and insert blank rows in between them?

Hello_there — Tue, 29 Nov 2022 23:37:27 GMT

Hi PaigeMiller, I am working on a table. I agree this is really unusual and it is a difficult-to-work-with arrangement, but that was what requested and that's what i have to work with unfortunately.

The actual problem is the values (group and param) are in different columns but on the same row as the criteria and value (in the original data set). But it's supposed to look like so the group is on top of the param, and both of those are in the same column on top of the criteria text and the word "count". This is difficult for me bc in my real life example there are many combos of group and param and it doesn't do well for me to hard code. I was hoping some others had encountered a similar problem and could share their wisdom on how to navigate this.

I changed the table for what it's supposed to be closer like in the OP.

Re: How do i move these values to the intended specific rows and insert blank rows in between them?

Tom — Wed, 30 Nov 2022 03:21:34 GMT

If you are just trying to print a report just use a data step and the PUT statement.

data _null_;
  set have;
  put group / param / criteria / 'count' @20 value / ;
run;

colors
red
>3
count              2

colors
blue
<3
count              3

cars
ford
>55
count              34

cars
chevy
>23
count              45

Re: How do i move these values to the intended specific rows and insert blank rows in between them?

Hello_there — Wed, 30 Nov 2022 06:11:55 GMT

Hi Tom,

I'm actually trying to create a data set. But thanks for showing me this.

Re: How do i move these values to the intended specific rows and insert blank rows in between them?

Hello_there — Wed, 30 Nov 2022 06:45:31 GMT

The best thing i could come up (sort order matters in the end as it will be sorted BY GROUP PARAM CRITERIA). I say this because currently this doesn't match the desired output in the OP, but that's ok.

data want; set have; output; output; output; run;
proc sort; by group param criteria; run;

data want1; set want;
   by group param criteria;
    if first.param and first.criteria then count=0; count +1;
    
    if count=1 then do;
    	criteria=group;
    	value="";
    end;
    if count=2 then do;
    	criteria=param;
    	value="";
   	end;
   	
   	keep criteria value;
run;