BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

Hi SAS Forum,

Another worker has written below compacted code.

/*COMPACTED CODE*/

%let age0=1303;

%let yymm=1303 1304 1305 1306 1307  ;

proc sql;

create table OUR_age0 as

select               a.*,

                     b.LOAN_LIMIT,

                     b.DEPOSIT,

                     "&age0" as year_month

from   (select       a.*

                     from   LOCATION.FILE_1    as             a inner join

               ABCD.INCOME_&age0  as             b on

               a.ACT_NO=b.ACCOUNT_NUMBER

                     where b.STATUS^="CLOSED")    as            a inner join

                     ABCD.EXPENSES_&age0      as b on a.ACT_NO=b.ACCOUNT_NUMBER

;

quit;

/*I HAVE SPLIT ABOVE COMPACTED CODE INTO simple 2 PIECES*/

/*PIECE 1 – YELLOW PORTION ABOVE*/

Proc sql;

Create table any_name as

select        a.*

              from   LOCATION.FILE_1      as            a inner join

                     ABCD.INCOME_&age0    as            b on

                     a.ACT_NO=b.ACCOUNT_NUMBER

                     where b.STATUS^="CLOSED";

quit;

run;

/*PIECE 2*/

proc sql;

create table OUR_age0_testing as

select               a.*,

                     b.LOAN_LIMIT,

                     b.DEPOSIT,

                     "&age0" as year_month

from   any_name             as            a inner join

       ABCD.EXPENSES_&age0        as            b on

  1. a.ACT_NO=b.ACCOUNT_NUMBER

;

Quit;

Run;

/*QUESTION*/

Final files produced by the compacted code and split two pieces have to be identical but following shows it is not.

proc compare base=Our_age0_testing compare=Our_age0 ;run;

Could anyone help me to split the compacted code correctly?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Rhodochrosite | Level 12

OK, my bad!

I misunderstood your original question.

You are asking how to re-write the query for testing purposes, while my answer was primarily related to Parsing the entire query, from a SQL syntax point of view!

Here is what I think the correct re-write should be:

%let age0=1303;

%let yymm=1303 1304 1305 1306 1307  ;

PROC SQL;

    CREATE TABLE Table1 AS /* Original In-Line Query named as Table1 */

    SELECT    a.*

         FROM    LOCATION.FILE_1 as a inner join ABCD.INCOME_&age0 AS b

         ON    a.ACT_NO=b.ACCOUNT_NUMBER

         WHERE b.STATUS^="CLOSED";

        

     CREATE TABLE OUR_age0_testing AS

    SELECT    a.*,

          b.LOAN_LIMIT,

          b.DEPOSIT,

          "&age0" as year_month

    FROM        Table1 /* Using Table1 as a replacement to the original In-Line Query */

     INNER JOIN ABCD.EXPENSES_&age0 AS b

         ON a.ACT_NO=b.ACCOUNT_NUMBER;

QUIT;

Thanks,

Ahmed

View solution in original post

4 REPLIES 4
AhmedAl_Attar
Rhodochrosite | Level 12

I would recommend using Regular Expression to extract the "Compacted Code" into separate file.

That could be one solution to your question.

You are asking for help in Parsing standard SQL query syntax.

Alternatively, search the Web for Open Source SQL Query Parser.

Good luck,

Ahmed

Mirisage
Obsidian | Level 7

Hi Ahmed,

Thank you for your response.

However, my level is not enough to understand what you suggested. I googled what is "Regular Expression". It looks like an another entire areas which I have not even touched.

Could you or someone help me to understand what is the wrong I have done in splitting the compacted code (which has a sub query) into the two simple pieces?

Thanks for the expertise and time.


AhmedAl_Attar
Rhodochrosite | Level 12

OK, my bad!

I misunderstood your original question.

You are asking how to re-write the query for testing purposes, while my answer was primarily related to Parsing the entire query, from a SQL syntax point of view!

Here is what I think the correct re-write should be:

%let age0=1303;

%let yymm=1303 1304 1305 1306 1307  ;

PROC SQL;

    CREATE TABLE Table1 AS /* Original In-Line Query named as Table1 */

    SELECT    a.*

         FROM    LOCATION.FILE_1 as a inner join ABCD.INCOME_&age0 AS b

         ON    a.ACT_NO=b.ACCOUNT_NUMBER

         WHERE b.STATUS^="CLOSED";

        

     CREATE TABLE OUR_age0_testing AS

    SELECT    a.*,

          b.LOAN_LIMIT,

          b.DEPOSIT,

          "&age0" as year_month

    FROM        Table1 /* Using Table1 as a replacement to the original In-Line Query */

     INNER JOIN ABCD.EXPENSES_&age0 AS b

         ON a.ACT_NO=b.ACCOUNT_NUMBER;

QUIT;

Thanks,

Ahmed

Mirisage
Obsidian | Level 7

Hi Ahmed,

Thank you for your help.

Miris

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 853 views
  • 0 likes
  • 2 in conversation