topic Re: Data extraction with counts and sum with similar remarks as common in SAS Enterprise Guide

Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 07:50:35 GMT

I have a data with some remarks field as common, I want to know the count of that data and the sum

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 07:59:12 GMT

Hi and welcome to the communities!

Please give us an example for your dataset, ideally in a data step with datalines (so we can easily recreate it for testing).

Use the "little running man" button (right next to the one indicated) to post your code:

The one indicated is best for logs and text data (e.g. from CSV files).

And show us what you expect as a result. If you already tried code to solve your issue, post that also.

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 09:53:30 GMT

I want to know how many C type of apples and the count and sum of the amount... etc

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 10:10:33 GMT

Do you think of something like this:

data have;
infile '/folders/myfolders/datasas.csv' dlm=',' dsd truncover;
input date :mmddyy10. amount name $ type :$1.;
format date yymmddd10.;
run;

proc sql;
create table want as
  select
    name,
    type,
    count(name) as count,
    sum(amount) as amount
  from have
  group by name, type
;
quit;

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 10:41:06 GMT

Q1. I am using windows 10 and the datasas file is on my desktop "C:\Users\prathap\Desktop"; how to map it

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 10:54:35 GMT

Do you have a licensed SAS installation, or University Edition?

If licensed, locally on your desktop or on a server?

If server, do you use SAS Studio or Enterprise Guide?

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 10:59:39 GMT

Licensed SAS installation, on Server, using Enterprise Guide.

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 11:10:51 GMT

Use the File Copy task in Enterprise Guide to copy the file to your server.

Then you can run the code I used, only adapting the location of the csv file.

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 11:22:42 GMT

It is not showing .csv file, it is accepting only SAS database files while browsing to upload file to server.

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 11:24:17 GMT

can I write steps like using query builder...

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 11:36:00 GMT

I am NOT talking about the dataset upload/download, I am talking about the COPY FILES TASK, which is part of the standard EG setup at least since version 7.13. This task can copy ANY file.

Re: Data extraction with counts and sum with similar remarks as common

Jprathap447 — Wed, 24 Jun 2020 11:38:59 GMT

I am using 4.3. Thanks for your support.

Re: Data extraction with counts and sum with similar remarks as common

Kurt_Bremser — Wed, 24 Jun 2020 11:51:25 GMT

If you like to live in the past, why do you use Windows 10 instead of XP, which is about as old?

Really, you should update to 7.15 or 8.2 ASAP, you're missing out on a tankerload of features.

And the current version is always included in SAS licenses.

Read the blog article, @ChrisHemedinger also provides the task as an add-on to earlier EG versions.

Without the task, you can always use a SFTP client to upload data to your SAS server, or use a shared network resource that is available both on your desktop and server. In the end, the file has to be "visible" to your SAS session, so you can run my code.

You can also use the import wizard of EG, but you need to supply the column names, types and formats yourself, since your csv file does not have a header line.

Re: Data extraction with counts and sum with similar remarks as common

ChrisHemedinger — Wed, 24 Jun 2020 17:12:31 GMT

Hi @Jprathap447 -

Welcome to the community and to the world of SAS. The version of SAS Enterprise Guide that you're using is quite old, but you still might benefit from this tutorial. It's 2+ hours and covers what you need to know about working within the environment, and completing basic tasks like querying and summarizing data.