BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

Assuming that columns with underscore has passed several conditions. I want the rows in the second data set overwrite the one in the first data set. The biggest problem here is that some values don't match because of variable columns (_) in the first and second data set. Another problem is the multiplying rows. I don’t want rows to multiply. The proc sql that i have created below combines the data sets however multiplies the rows. Can you help me about this problem? I have attached the code and the image so it might help you. 

 

data Main_SQL;
length InstanceID $ 20 Default 8 Q0001_ 8 Q0002_ 8 Q0003 8 Q0004_ 8 Q0005 8 Q0006 8;
infile datalines missover dlm=",";
input InstanceID Default Q0001_ Q0002_ Q0003 Q0004_ Q0005 Q0006 ;
datalines;
InstanceID1,0,0.90,0.80,0.00,0.90,0.00,0.70
InstanceID2,0,0.100,0.100,0.00,0.70,0.00,60
InstanceID3,1,0.40,0.80,0.00,0.90,0.00,0.50
InstanceID4,0,0.55,0.80,0.05,0.90,0.00,0.69
InstanceID5,0,0.00,0.80,0.60,0.90,0.20,0.90
InstanceID6,1,0.96,0.00,0.40,0.90,0.00,0.95
InstanceID7,0,0.00,0.80,0.90,0.90,0.00,0.99
InstanceID8,1,0.56,0.80,0.55,0.90,0.00,0.93
InstanceID9,0,0.99,0.80,0.99,0.90,0.00,0.70
InstanceID10,0,0.89,0.88,0.56,0.90,0.00,0.00
;
run;
Proc means data=Main_SQL STACKODS noprint
FW=12
PRINTALLTYPES
CHARTYPE
QMETHOD=OS
VARDEF=DF
	MEAN
	STD
	MODE
	P10
	P90 ;
VAR Q:;
ODS OUTPUT Summary=backTestGroup2;
run;
data backTestGroup2;
  set backTestGroup2;
  UPPER =((P90-MODE)/(P90-P10))*STDDEV+MODE;
  LOWER =((MODE-P10)/(P90-P10))*STDDEV+MODE;
  CENTER=(UPPER+LOWER)/2;
  SLOPE =2.95/(UPPER-CENTER);
  keep Variable Mean Stddev Center Slope;
run;
data ozMeans;
length Variable $ 12 Mean 8 Stddev 8 Center 8 Slope 8;
infile datalines missover dlm=",";
input Variable Mean Stddev Center Slope;
datalines;
Q0002_,0.700,0.12227662376012,0.56113831188006,-5.3700431505101
Q0004,0.535,0.398281028644069,0.199140514322035,14.8136606458166
Q0006_,0.658,0.32227662376012,0.96113831188006,-20.3700431505101
;
PROC SQL;
Create table backTestGroup2 as
Select* from(Select * from ozMeans 
union all
select * from backTestGroup2
where left(trim(Variable)||"_") not in(Select Variable from ozMeans)) AA
order by aa.variable;
QUIT;
1 ACCEPTED SOLUTION

Accepted Solutions
hbi
Quartz | Level 8 hbi
Quartz | Level 8

If you use "upcase()", your results should be consistent with the one I provided.

 

change:  TRANSTRN(Variable, "_", "")

    to:  UPCASE(TRANSTRN(Variable, "_", "")) 

 

See below:

 

 

PROC SQL;
 /* CREATE TABLE backTestGroup2 AS */
    CREATE TABLE backTestGroup3 AS 
    SELECT Variable  length=20
         , COUNT(DISTINCT CATX('|', Mean, Stddev, Center, Slope, Mean)) AS num_distinct_values
         , AVG(Mean)   AS Mean_Mean
         , AVG(Stddev) AS Mean_Stddev
         , AVG(Center) AS Mean_Center
         , AVG(Slope)  AS Mean_Slope
         , CASE WHEN calculated num_distinct_values > 1 
                THEN '<== Note: summary statistic is an average (more than one unique row was returned)'  
           ELSE '' END AS remark length=100
    FROM (SELECT DISTINCT UPCASE(TRANSTRN(Variable, "_", "")) AS Variable, Mean, Stddev, Center, Slope
          FROM ozMeans
          UNION
          SELECT DISTINCT UPCASE(TRANSTRN(Variable, "_", "")) AS Variable, Mean, Stddev, Center, Slope
          FROM backTestGroup2
          WHERE UPCASE(TRANSTRN(Variable, "_", "")) 
                   NOT IN (SELECT UPCASE(TRANSTRN(Variable, "_", "")) FROM ozMeans)
         ) As InnerQry
    GROUP BY Variable
    ORDER BY 1;
QUIT;

 

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

Well, I gues that you need to make your Q columns names match somehow. Compressing "_" whould be simple, but perhaps not on your real data?

Then I would use data step - modify by to update the master data set, should not generate duplicates.

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

You mean you want to use the values from OZMEANS when they exist or use the other table.  If so the below code will do that, the trick is putting the update first in the COALESCE() order, so it will be taken if it exists.  Do note, consisten formatting, casing, indetation, makes code far more readable.

data main_sql;
  length instanceid $ 20 default 8 q0001_ 8 q0002_ 8 q0003 8 q0004_ 8 q0005 8 q0006 8;
  infile datalines missover dlm=",";
  input instanceid default q0001_ q0002_ q0003 q0004_ q0005 q0006 ;
datalines;
InstanceID1,0,0.90,0.80,0.00,0.90,0.00,0.70
InstanceID2,0,0.100,0.100,0.00,0.70,0.00,60
InstanceID3,1,0.40,0.80,0.00,0.90,0.00,0.50
InstanceID4,0,0.55,0.80,0.05,0.90,0.00,0.69
InstanceID5,0,0.00,0.80,0.60,0.90,0.20,0.90
InstanceID6,1,0.96,0.00,0.40,0.90,0.00,0.95
InstanceID7,0,0.00,0.80,0.90,0.90,0.00,0.99
InstanceID8,1,0.56,0.80,0.55,0.90,0.00,0.93
InstanceID9,0,0.99,0.80,0.99,0.90,0.00,0.70
InstanceID10,0,0.89,0.88,0.56,0.90,0.00,0.00
;
run;
proc means data=main_sql stackods noprint fw=12 printalltypes chartype qmethod=os vardef=df mean std mode p10 p90;
  var q:;
  ods output summary=backtestgroup2;
run;
data backtestgroup2;
  set backtestgroup2;
  upper =((p90-mode)/(p90-p10))*stddev+mode;
  lower =((mode-p10)/(p90-p10))*stddev+mode;
  center=(upper+lower)/2;
  slope =2.95/(upper-center);
  keep variable mean stddev center slope;
run;
data ozmeans;
  length variable $ 12 mean 8 stddev 8 center 8 slope 8;
  infile datalines missover dlm=",";
  input variable mean stddev center slope;
datalines;
Q0002_,0.700,0.12227662376012,0.56113831188006,-5.3700431505101
Q0004,0.535,0.398281028644069,0.199140514322035,14.8136606458166
Q0006_,0.658,0.32227662376012,0.96113831188006,-20.3700431505101
;
run;

proc sql;
  create table RESULT as
  select  COALESCE(A.VARIABLE,B.VARIABLE) as VARIABLE,
          COALESCE(A.MEAN,B.MEAN) as MEAN,
          COALESCE(A.STDDEV,B.STDDEV) as STDDEV,
          COALESCE(A.CENTER,B.CENTER) as CENTER,
          COALESCE(A.SLOPE,B.SLOPE) as SLOPE
  from    OZMEANS A
  full join BACKTESTGROUP2 B
  on      strip(tranwrd(A.VARIABLE,"_",""))=strip(tranwrd(B.VARIABLE,"_",""));
quit;
turcay
Lapis Lazuli | Level 10

Thank you very much for your support. You have got me right but i couldnt do overwriting on the code you have sent. I think youe code multiplied too. I want it to be 6 rows totally.

 

Thanks a lot 🙂

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Did you run the code I posted, as when I run it I got 6 records.  Post your output if it doesn't work and the code you use.

turcay
Lapis Lazuli | Level 10

Hi RW9,

 

Am i doing something wrong. The result of table is as follows:

 

Try.png

hbi
Quartz | Level 8 hbi
Quartz | Level 8

Hi there,

 

This should be pretty close to what you are looking for. Note that for "Q0002" and "Q0006", multiple summary statistics are returned, so you will need to use another summary function such as MIN(), MAX() or AVG(). In this example, I used AVG, but you can use MIN or MAX if that better suits your needs. 

 

PROC SQL;
 /* CREATE TABLE backTestGroup2 AS */
    CREATE TABLE backTestGroup3 AS 
    SELECT Variable  length=20
         , COUNT(DISTINCT CATX('|', Mean, Stddev, Center, Slope, Mean)) AS num_distinct_values
         , AVG(Mean)   AS Mean_Mean
         , AVG(Stddev) AS Mean_Stddev
         , AVG(Center) AS Mean_Center
         , AVG(Slope)  AS Mean_Slope
         , CASE WHEN calculated num_distinct_values > 1 
                THEN '<== Note: summary statistic is an average (more than one unique row was returned)'  
           ELSE '' END AS remark length=100
    FROM (SELECT DISTINCT TRANSTRN(Variable, "_", "") AS Variable, Mean, Stddev, Center, Slope
          FROM ozMeans
          UNION
          SELECT DISTINCT TRANSTRN(Variable, "_", "") AS Variable, Mean, Stddev, Center, Slope
          FROM backTestGroup2
          WHERE TRANSTRN(Variable, "_", "") NOT IN (SELECT Variable FROM ozMeans)) As InnerQry
    GROUP BY Variable
    ORDER BY 1;
QUIT;

 

Here is the output: 

(edited: updated image; old output was incorrect)

 

proc_sql_union_mean_of_aggregates_(fixed).gif 

Enjoy. 

 

-- hbi

turcay
Lapis Lazuli | Level 10

Thank you very much. But i'm not sure we receive the same results. Because when i run your code i got the data set as below.But your codes give some idea to me to write result which i want it. Thanks a lot @hbi 🙂

 

hbi.png

 

Your advices seems to be fine. Can you give some sample data set or share with me a link ? @LinusH

hbi
Quartz | Level 8 hbi
Quartz | Level 8

If you use "upcase()", your results should be consistent with the one I provided.

 

change:  TRANSTRN(Variable, "_", "")

    to:  UPCASE(TRANSTRN(Variable, "_", "")) 

 

See below:

 

 

PROC SQL;
 /* CREATE TABLE backTestGroup2 AS */
    CREATE TABLE backTestGroup3 AS 
    SELECT Variable  length=20
         , COUNT(DISTINCT CATX('|', Mean, Stddev, Center, Slope, Mean)) AS num_distinct_values
         , AVG(Mean)   AS Mean_Mean
         , AVG(Stddev) AS Mean_Stddev
         , AVG(Center) AS Mean_Center
         , AVG(Slope)  AS Mean_Slope
         , CASE WHEN calculated num_distinct_values > 1 
                THEN '<== Note: summary statistic is an average (more than one unique row was returned)'  
           ELSE '' END AS remark length=100
    FROM (SELECT DISTINCT UPCASE(TRANSTRN(Variable, "_", "")) AS Variable, Mean, Stddev, Center, Slope
          FROM ozMeans
          UNION
          SELECT DISTINCT UPCASE(TRANSTRN(Variable, "_", "")) AS Variable, Mean, Stddev, Center, Slope
          FROM backTestGroup2
          WHERE UPCASE(TRANSTRN(Variable, "_", "")) 
                   NOT IN (SELECT UPCASE(TRANSTRN(Variable, "_", "")) FROM ozMeans)
         ) As InnerQry
    GROUP BY Variable
    ORDER BY 1;
QUIT;

 

turcay
Lapis Lazuli | Level 10

Thanks a lot. It worked.I'll examine whole of the code in detail. Thanks for leaded me.

 

Can.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1648 views
  • 1 like
  • 4 in conversation