topic Re: How to Add .sas file to query in SAS Studio

How to Add .sas file to query

Macy — Sun, 27 Nov 2016 17:20:33 GMT

Hi, I have a probolem regarding sas file.

i have a sas file and i want to add to query, to merge data, however it is no options to choose from home folder, only can used the one from library,but because myfile is .sas file, i cnnot add to library, then what shall i do the add it into query ?

Re: How to Add .sas file to query

Shmuel — Sun, 27 Nov 2016 18:18:42 GMT

.sas file is aprogram file.

What do you want to do by adding it to a query?

What SAS platform are you using?

What do you have in your query? and what does the .sas file contains ?

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:23:18 GMT

Hi, I have a sas file called GermanCreditV2Combination.sas, in this file i combined several binary variables, then i want to link it to another data set to use these combined variables.

So i create a query and try to link them together, but i find i have no option to attach .sas file.

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:23:24 GMT

Hi, I have a sas file called GermanCreditV2Combination.sas, in this file i combined several binary variables, then i want to link it to another data set to use these combined variables.

So i create a query and try to link them together, but i find i have no option to attach .sas file.

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:29:00 GMT

another thing i forget to mention, i use sas studio. and i want to create analytical base table in sas, so i create query to do that.

then i try to link the combination sas file with the original data set to merge the binary variable, but i'm not quite sure whether my step is correct or not ?

Re: How to Add .sas file to query

Shmuel — Sun, 27 Nov 2016 18:36:55 GMT

@Macy, in the posted .png files you show the output you have.

As .sas file is a program file it should be included in the CODE ,

maybe in as:

%include "..path and name to the .sas file ..";

To help you more upload and post your query code and the .sas file code.

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:45:52 GMT

if i want to query ABT and create ABT, is the step i follow correct ? Becasue i am not quite sure.

Or some other options i can choose to make it ?

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:51:08 GMT

and i am trying to write code in query, but it does not allow me to do that, i donot know why.

Attached is the sas file i want to merge with the data.

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 18:59:55 GMT

Hi,

The most important thing is i want to follow as attached process flow, as you can see in the middle bottom, it shows 'query ABT 'and upper middle it shows 'create ABT'. But firstly, i do not know how to query ABT and how to write code in qury, becuse i suppose in the query ABT i shall write the code to combine the different dataset tegether, but as i mentioned, it does not allow me to add .sas file. so i d not know how to add this file to query ABT.

secondly, i have transformed the numerical variable, i want to include them in the ABT, but i donot know how to include thme into ABT, shall i write the code in query ?

Re: How to Add .sas file to query

Shmuel — Sun, 27 Nov 2016 19:04:15 GMT

Now I can see that you created the work.car output dataset using proc sql.

You did not post the code you have in GermanCreditV2Combination.sas program file.

Post it and i will show you how to chain it to you query, the proc sql you have.

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 19:04:56 GMT

hi,

here is the code use to create the combination data

PROC SQL;
CREATE TABLE Work.Car
AS
SELECT OBS, GermanCreditV2.New_Car,GermanCreditV2.Used_Car
,(CASE
WHEN (New_Car = 1 or Used_Car = 1) THEN 1
ELSE 0
END) as Car
,(CASE
WHEN (MaleDiv = 1 or MaleSingle = 1 or MaleMarorWid = 1) THEN 1
ELSE 0
End) as Sex
,(CASE
WHEN (Education = 1 or Retraining = 1) THEN 1
ELSE 0
End) as NewEducation
,(case
WHEN (Guarantor = 1 or APPLICANT = 1) THEN 1
ELSE 0
END) AS NewApplicant
,(CASE
WHEN (Furniture = 1 or Radio = 1) THEN 1
ELSE 0
END) AS Appliance
FROM GC.GermanCreditV2;
QUIT;

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 19:05:57 GMT

hi,

here is the code use to create the combination data

Re: How to Add .sas file to query

Shmuel — Sun, 27 Nov 2016 19:26:42 GMT

I'm trying to understand the situation:

Your first code is - GermanCreditV2Combination.sas:

PROC SQL;
CREATE TABLE Work.Car
AS
SELECT  OBS, GermanCreditV2.New_Car,GermanCreditV2.Used_Car
,(CASE
     WHEN (New_Car = 1 or Used_Car = 1) THEN 1
     ELSE 0
     END) as Car
     ,(CASE
     WHEN (MaleDiv = 1 or MaleSingle = 1 or MaleMarorWid = 1) THEN 1
     ELSE 0
     End) as Sex
     ,(CASE
     WHEN (Education = 1 or Retraining = 1) THEN 1
     ELSE 0
     End) as NewEducation
     ,(case
     WHEN (Guarantor = 1 or APPLICANT = 1) THEN 1
     ELSE 0
     END) AS NewApplicant
     ,(CASE
     WHEN (Furniture = 1 or Radio = 1) THEN 1
     ELSE 0
     END) AS Appliance
     FROM GC.GermanCreditV2;
QUIT;

you wrote:

I have a sas file called GermanCreditV2Combination.sas, 
in this file i combined several binary variables, 
then i want to link it to another data set to use these combined variables.

You should distinguish between a program (proc sql or data set) and a sas dataset (like work.car).

What do you mean by

i want to link it to another data set

what is this "another data set" ? what variables are in it ? what variables are in common with work.cars dataset ?

Re: How to Add .sas file to query

Macy — Sun, 27 Nov 2016 19:32:07 GMT

oh, i misunderstand it. j ust figure out and query ABT as attached, i merge these two datasets together.

Now i want to create ABT, do you know how to do that, and how shall i link the trasform data with ABT ?

Re: How to Add .sas file to query

Shmuel — Sun, 27 Nov 2016 19:38:13 GMT

So you have merged your two datasets.

You are coming back to another your post:

https://communities.sas.com/t5/SAS-Studio/How-to-create-ABT-in-Sas-Studio/m-p/314621

I'm not expert in analytics. Someone else in the forum will help you.